Error: Referencing using activex command button

Hello,

I have a problem with referencing I think..

I have an activex command button in my spreadsheet which I want to run code...

The code runs fine if I run it from a userform.. or If I have the code in a userform.. get the command button to load the form and on form initialize the code is in there.

The only problem this way is that I can't get the form to unload itself properly once the code has ran.

If i put unload me at the end of the code I'm getting "object variable or with block variable not set" and the debug highlights my form.show code from the command button "frmirisonline.Show"

If I run all of the code straight from the command button which is what I'd like to do I get error 1004 "The worksheet range for the table data must be on the same sheet as the table being created"

the problem starts here

Formula:

Please Login or Register  to view this content.

the code which starts with activesheet doesnt recognise Sheets("data") as the active sheet.

When you do step-by-step with F8 does it select the "DATA" Sheet?

The code appears to work fine when I loaded it into a workbook -- something from before must be messing it up? Somehow...?

You could try explicity referencing Sheets("Data") instead of using ActiveSheet.

That might not work though because of the new workbook you've created when you run this code.

Please Login or Register  to view this content.

You might need to add a reference to the newly created workbook too.

Hi,

When I use F8 to step through it is selecting a new workbook and the correct sheet where I want the data.

But then when it gets to the code with activesheet it said "application-defined or object-defined error"

I'll try to play about with referencing the new workbook

Do you actually mean to create a new workbook?

If you do why not do it like this.

Please Login or Register  to view this content.

Have you considered the problem might be something else?

Perhaps something to do with ListObjects.Add.

Can you post the rest of the code?