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:
- 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"
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
Save the file as an macro-enabled
document and call it
"MacroXSLX.xlsm". Leave it open.
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
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.