Still thinking about the combo box idea, but just came accross this:
http://www.exceltip.com/at-424|Menus%2C_Toolbars%2C_Status_bar_in_VBA-Pass_arguments_to_macros_from_buttons_and_menus_using_VBA_in_Microsoft_Excel
Which give the key line of code to pass a parameter (in this case, a caption name) as:
.OnAction = "'MyMacroName2 """ & .Caption & """'"
Just what I need! Only, I can't get it to work.
This is my code... any ideas? When I run it I don't get an error, but the macro doesn't run either.
Sub AddSupportSheetsSubMenu(mm As CommandBarPopup, blnBeginGroup As Boolean)
Dim sh As Worksheet
Dim m As CommandBarPopup, mi As CommandBarButton
If mm Is Nothing Then Exit Sub
' create the submenu
Set m = mm.Controls.Add(msoControlPopup, , , , True)
With m
.BeginGroup = blnBeginGroup
.Caption = "Support and scratch sheets..."
End With
For Each sh In Worksheets
' add a menu item
Set mi = m.Controls.Add(msoControlButton, , , , True)
With mi
.Caption = sh.Name
.OnAction = "'MyMacroName2 """ & .Caption & """'"
.FaceId = 66
.Style = msoButtonIconAndCaption
End With
End If
Next sh
Set mi = Nothing
Set m = Nothing
End Sub
....
Sub MyMacroName2(SheetName as String)
Sheets(SheetName).Select
End Sub