Running Excel Forms VBA Code in separate sheet|VBA & Macros|Excel Forum|My Online Training Hub

Hi,

I was following your Excel Forms tutorial available at this URL to create a Dairy Farm Data Management system in Excel. The code is working fine if the "Add Expense" button is placed within the "Expense Data" sheet. However I want to place the button in the sheet named "Module" and then make the form add data into the sheet named "Expense Data".

I tried the modification suggested by you in this comment:

Hi Kara,
Of course it’s possible, here is the place where you can change the destination:
‘Add row to bottom of Expenses table
ActiveSheet.ListObjects(“Expenses”).ListRows.Add

‘Enter data from form into our new row
Set ExpenseTable = ActiveSheet.ListObjects(“Expenses”)

You can change it to:

‘Enter data from form into our new row
Set ExpenseTable = ThisWorkbook.Worksheets(“Sheet1”).ListObjects(“Expenses”)
‘Add row to bottom of Expenses table
ExpenseTable.ListRows.Add

You should create the table in the sheet you want before running the code, it is expecting to find a table there, it will not create it from code.

However, I am getting “Compile Error: Variable not defined”. Can you please advice? I am attaching my workbook. Thanks.