How to call sheet-specific macro from one Excel workbook in another?

I know you've figured this out, probably after much hair-tearing and coffee, but I wanted to:

• Give you more details on why this is
• Provide you with a way you could use your sheet's name to get what you want.

First of all, the worksheet name you are wanting is not the same thing as the code module name. So in your VBE, you see that the name of the code module is "Sheet1", but if may have a different property of Name which is different, for example, "MySheet1" (or it also may be the same).

In order to get it by name, you'll have to do some loops, change security settings, etc. If that's what you're after (this works well in smaller environments because of the security setting issue), here you go as an example:

1. Change your security settings to trust programmatic access to VBA Projects. In Excel 2007, go to Orb | Excel Options | Trust Center | Trust Center Settings | Macro Settings and then enable "Trust access to the VBA project model"

2. Create a workbook with one worksheet. Rename it "MySheet1". Open the VBE (Alt+F11) and in "Sheet1 (MySheet1)" create a sub routine, call it TimesTen and in the code just put Debug.Print 10 * 10. Like this:

   Sub TimesTen()
       Debug.Print 10 * 10
   End Sub
   

3. Save the file as an macro-enabled document and call it "MacroXSLX.xlsm". Leave it open.

4. Open a new Excel document, navigate to it's VBE and in a new macro anywhere, create a sub called Test. In the body of that code, put this: .

   Sub test()
   Dim SheetName As String
   SheetName = "MySheet1"
   Dim wb As Workbook
   Set wb = Workbooks("MacroXSLX.xlsm")
   For Each VBComp In wb.VBProject.VBComponents
       If VBComp.Properties.Item("Name").Value = SheetName Then
           Application.Run (wb.Name & "!" & VBComp.Name & ".TimesTen")
       End If
   Next
   End Sub
   

5. Press F5 to run test and you should see 100 in the Immediate window.

You can see in #4 that I'm looping through all the components (Modules, Classes, etc.) looking for the one that has a Name property that has a value of MySheet1. Once I have that, I can then get the name of that component, which in this case is Sheet1 and use that to construct my string that will run the sheet's macro in MacroXSLX.xlsm. The code can be cleaned up further to exit the For statement when you've found what you want, etc.

As mentioned above, the only real draw-back to this is the security settings piece and ensuring you have programmatic access to the VBAProject - fine on one to ten computers, but could be a hassle if you have to ensure more than that are always set correctly.