Custom Menus and OnAction

[Kent Prokopy]

I have the following code.


Private Sub Workbook_Open()
' Add menu item for Finops.
With Application.CommandBars("WorkSheet Menu
Bar").Controls.Add(Type:=msoControlPopup)
.Caption = "MyMenu"

' Add menu items for showing and hiding groups of sheets.
With .Controls.Add(msoControlPopup, 1)
.BeginGroup = True
.Caption = "Options"

' Sub Menus.
With .Controls.Add(msoControlButton, 1)
.Caption = "Option1"
.Tag = 0
.OnAction = "modMain.DoSomeThing"
End With

With .Controls.Add(msoControlButton, 1)
.Caption = "Option2"
.Tag = 1
.OnAction = "modMain.DoSomeThing"
End With

End With

End With
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("WorkSheet Menu Bar").Controls("MyMenu").Delete
End Sub

When I select "Option1" or "Option2" from my custom menu and the Sub
DoSomeThing is executed. I need to know what sub menu called it/was clicked.
Both sub menus call the same Sub.

 

[Bob Phillips]

Sub DoSomeThing()

Select Case Application.CommandBars.ActionControl.Caption

Case "Option1"

'do option 1's thing

Case "Option2"

'do option 2's thing
End Select

End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mike H.]

Why not just call 2 different subs that then in turn call the current sub you
are now calling. Then you would know which one called it because you'd have
that information before you call the sub. To do this you could use a public
variable that you set to either 1 or 2. Then in the subsequent sub that is
called the value would be either 1 or 2, depdning on which sub was called
before you get to the final sub. HTH

To declare a public variable, just go to the area above the first sub and
type:
Public WhichOneCalled as integer
These 3 subs would all be in your modmain module.

 

[Kent Prokopy]

I could not find this online anywhere.
Application.CommandBars.ActionControl.Caption

YOU ARE MY HERO...

Thank you

 

[Bob Phillips]

ActionControl refers to the selected button, so it gives you access to all
of its properties, as well as Caption.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)