Run Macro from other wb with keystroke

[energywatch]

Hi, I'm new to the forum. Thank you in advance for your help and sorry if I do not follow protocol (I am still learning).

I am trying to build some macros in my own workbook to be able to improve efficiency to a protected workbook that can not be changed directly. I would like to:

- While in 'macros.xlsm' file
- Use 'Shift+B' to go to workbookM.xlsm
- Run "MacroB"

So far I have used:

.OnKey Key:="^+B", Procedure:="MacroB" (doesnt work)

and

Application.Run "workbookM.xlsm!MacroB" (doesnt work)

So far the keystroke gives me an error "Cannot run the macro "C:___". The macro may not be available in this workbook or all macros may be disabled."

When I run through the VBA using F8 I successfully get to MacroB. But, it breaks when the macro starts to refer to sheets by name. Do I need to activate workbookM? If so how?


Thank you for all of your help,
ew

 

[Col Delane]

Hi EnergyWatch and welcome to the Forum.

Does the keystroke have to be Shift+B or can it be something else, like Ctrl+[your choice]?

You also need to assign the keystroke shortcut to a macro within 'macros.xlsm' like:

MySub()
[INDENT][/INDENT]Workbooks.Open("workbookM.xlsm")
End Sub

You may need to include a path if it's not in the current directory.

Once you have created that macro, press Alt+F8 from the worksheet in Excel to open the Macros dialogue box and select the macro and then Options. In here you can select a shortcut key, but it seems it must be Ctrl+[your choice] (I thought you could change this previously to a different combination, but I couldn't see how!!)

Then in "workbookM.xlsm" you need a Workbook_Open() event macro in the ThisWorkbook object (last item under Microsoft Excel Objects list in the Project Window of the Visual basic Editor) like:

Workbook_Open()
[INDENT][/INDENT]Call MacroB
End sub

Once all this is done:

1. From 'macros.xlsm', when you press Ctrl+[your choice], "workbookM.xlsm" will open.
2. When "workbookM.xlsm" opens, the Workbook_Open() event macro will automatically fire, and execute MacroB via the Call MacroB statement

HTH

 

[energywatch]

This ended up working well. Thank you!