Specifying argument with OnAction in custom menu
Posted by Bill on December 21, 2000 11:11 AM
This is a bizarre problem. I want a custom menu to appear with the names of all 10 stores. When the user picks one of the stores, I want to call the same program with a different argument.
I built the menu with the following code:
Sub CreateMenu()
Dim MenuObject As CommandBarPopup
Dim MenuItem As Object
Set MenuObject = Application.CommandBars(1). _
Controls.Add(Type:=msoControlPopup, _
Before:=11, temporary:=True)
MenuObject.Caption = "&Customer"
Sheets("Menu").Select
ActiveSheet.Unprotect
For Each cell In Range("ValidStores").CurrentRegion
Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
MenuItem.OnAction = "ProcessStore(""" & cell.Value & """)"
MenuItem.Caption = cell.Value
Next
End Sub
When I try to select something from the menu, one of two things happens: In the cases where I made ProcessStore simple (msgbox StoreName) for example, it would always run twice. In cases where ProcessStore was more complex, calling other procedures, the proc would end without an error, but without hitting any breakpoints in the called procedures. Here is the simple version which runs twice:
Sub ProcessStore(WhichStore As Variant)
MsgBox WhichStore
End Sub
How can I specify a macro and pass a parameter from a custom menu item?
I realize this one is obscure, but I would appreciate any thoughts.
Bill