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



Posted by Tim Francis-Wright on December 21, 2000 2:19 PM

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

I found something at archive.baarns.com/excel/faq/xd_macr1.asp which
seemed to help.

1. This worked when the ProcessStore sub was
defined as Public Sub ProcessStore(WhichStore as Variant) .

2. Here are the two changes to your code:
Add :-
Dim dq as String
dq = Chr(34) '(double Quote)

MenuItem.OnAction = "'ProcessStore " & dq & cell.Value & dq & "'"

The single quotes seem to be important--however,
this means that a single quote in the name of
the store can't be passed effectively. Maybe
someone else knows how to imbed a single quote
in the store name (you might just have to use
a backquote instead)