Running a macro from an exported worksheet

[Ivan Howard]

Hi All,

Please could someone give me a hand with this.

I have an add-in that contains a calculation template. This is how it works:

1. Click a button on a toolbar;
2. Calculation template is copied from the .xla file into the active workbook;
3. Data is keyed manually/pasted into the template;

The problem is at this point. I have a macro saved in the template worksheet object (that has been copied over) that is linked to a button on the template. When I click on this button the macro should run to calculate/process the data.

Questions:
1. Why doesn't the button find the macro?
2. What text do I need to use in the "Assign macro" box to assign it to the macro within itself.

Sounds fairly simple, but struggling to get it working.

Thanks very much for reading.

 

[Andrew Poulsom]

If you use a CommandButton from the Control ToolBox, its Click event code will be in the module for the worksheet and will transfer when you copy it.

 

[Ivan Howard]

Hi Andrew,

This is exactly what I needed. Thanks very much!

 

[Ivan Howard]

Hi Andrew,

What you told me allows me to run the macro, however, the macro always results in a "Run-time error '1004'@ Select method of Range class failed" error when running a simple Range("A1").Select statment.

I have investigated it and from what I see it happens when running a macro within the worksheet object itself (whether the macro is public, private or normal) that interacts with other sheets.

Do you know what I am doing wrong?

I could get the macro to calculate the data within one worksheet, but not what I ideally wanted to do.

Thanks again for your help.

 

[RoryA]

1. You almost never need to actually select a range to manipulate it.
2. You should qualify all references to ranges with the sheet (and workbook) to which they belong - for example:

Dim wks as worksheet
set wks = thisworkbook.sheets("whatever")
wks.range("A1").value = "something"

 

[Ivan Howard]

Thanks Rory.

I have tried using your code and it works. However, as I am pasting to ranges on other sheets and then selecting that range to manipulate it, filter it, and so on, it is becoming increasingly difficult to do so with the restriction of not being able to actually select it.

My solution is to recreate the whole process using the one tab and the blank columns to the right of the data.

Thanks very much for your help.

 

[RoryA]

As I said, you don't need to select it.
However, if you want to, you need to activate the relevant sheet first and you must then specify the sheet for the range every time you refer to it. If you don't, a simple Range(...) call in a worksheet code module will always refer to a range on the worksheet containing the code, regardless of which sheet is active.

 

[Ivan Howard]

Thanks - this is helpful. I will try again and post back when done.

 

[Ivan Howard]

Thanks very much Rory - I updated the code with your suggestions and it works brilliantly!

The whole system is working how I need it to. Many thanks to you and Andrew!