CommandButton Control activate a macro in Personal Worksheet? 1

[dcanfield]

My manual states that a macro linked to a CommandButton control is located in the code module for the spreadsheet that the Commandbutton is located in.

I want my CommandButton Control to activate a macro in my personal Worksheet (as the macro moves data between several different spreadsheets, it needs to be located in the Personal worksheet).

Can this be done? If not, how can I get a macro located on one spreadsheet work when another worksheet is the active spreadsheet?

Thanks,

David

 

[PHV]

Have a look at the difference between ThisWorkbook and ActiveWorkbook.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TonyJollans]

Hi David,

If your button is an ActiveX button (from the Control Toolbox) its macro MUST be in the Sheet's module. You can, however, do what you like in that macro including working with other worksheets/workbooks and/or calling another macro in your Personal.xls if that's what you want to do ..

[blue]Application.Run "PERSONAL.XLS![i]MacroInPersonalWorkbook[/i]"[/blue]

I don't understand your last question exactly but macros can reference any worksheet - not just the active one.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[dcanfield]

Hi Tony,

I used your suggestion to have my local macro call a macro on my Personal Workbook. It worked just great. Thanks!

As that worked so well, and so easily, I didn't persue PHV's suggestion.

David