Excel - calling macro from custom toolbar

[cdipaolo]

I have a very simple macro in Excel that selects a sheet that is passed in - something like:

Sub GetSheet (strSheet As String)

Sheets(strSheet).Select

End Sub

This macro works running it by itself or if attaching to a command button. Now, when I create a custom menu and attach this macro to the .onAction of the menu item, it will not work. I have inserted a MsgBox command in the macro, so I know it's getting to it, but the other sheet is not being selected. Any ideas?

 

[RobBroekhuis]

How are you passing the argument to the sub (this question goes for both the commandbutton and the menu item)?
Rob

 

[aMember]

You need to do

.onaction = "DoGetSheet"


then define

sub DoGetSheet
GetSheet(your params here)
end sub

Sub GetSheet (strSheet As String)

Sheets(strSheet).Select

End Sub

 

[cdipaolo]

Thanks to both of you - turned out to be something really simple. Forgetting about passing a sheet, it wasn't working hard coded. The reason is that I was calling

.onAction = "GotoSheet()" when I should have put
.onAction = "GotoSheet"

The post from aMember made me think about my syntax. Thanks again for the quick replies!!