One Routine for Multiple Menu Events

[SJ McAbney]

Just playing with a bit of code to get a new menu option that lists all my workbbook's sheets. Thus far, not a problem.

However, as people may add further sheets to the workbook, I don't want to have a separate sub for each sheet in the ThisWorkbook module. Instead I'd like to pass the sheet's name, as a parameter, to one routine. Unforunately, as I've tried doing below in the OnAction event of a menu, macros with passed parameters cause an error.

Is there any way to resolve this?


Private Sub AddToWorksheetMB()
' Object Declarations
Dim cbWMB As CommandBar ' CommandBar Object
Dim cbctrlCustom As CommandBarControl ' CommandBarControl Object
Dim HelpMenu As CommandBarControl ' CommandBarControl Object
Dim wsTemp As Worksheet ' Worksheet object
' Delete current menu
Call DeleteFromWMB
Set cbWMB = Application.CommandBars(strMenuReference)
On Error Resume Next
Set HelpMenu = CommandBars(1).FindControl(ID:=30010)
On Error GoTo 0
If HelpMenu Is Nothing Then
'Help Menu does not exist therefore add new menu at end of menu toolbar
Set cbctrlCustom = cbWMB.Controls.Add(msoControlPopup)
Else
'Add menu after Help
Set cbctrlCustom = cbWMB.Controls.Add(msoControlPopup, HelpMenu.Index + 1)
End If
With cbctrlCustom
.Caption = strMenuName
For Each wsTemp In Worksheets
With .Controls.Add(msoControlButton)
.Caption = wsTemp.Name
.OnAction = ThisWorkbook.Name & "!ThisWorkbook.SelectSheet(" & wsTemp.Name & ")"
End With ' Controls
Next wsTemp
End With ' cbctrlCustom
Exit_AddToWorksheetMB:
Set wsTemp = Nothing ' Release Worksheet object from memory
Set cbWMB = Nothing ' Rlease CommandBar object from memory
Set cbctrlCustom = Nothing ' Release CommandBarControl object from memory
Set HelpMenu = Nothing ' Release CommandBarControl object from memory
Exit Sub ' AddToWorksheetMB
Err_AddToWorksheetMB:
Resume Exit_AddToWorksheetMB
End Sub ' AddToWorksheetMB

[Killian]

I've done a similar thing before. Call the single routine and rather than pass a paramater, use the Commandbars.ActionControl property within the routine - the ActionControl returns the CommandBarControl object whose OnAction property is set to the running procedure - so if your parameter is the control caption you can use
CommandBars.ActionControl.Caption

[SJ McAbney]

Can you give an example as I'm getting the error that the object is not set and I don't see where to set it.

[Killian]

I was getting an error with your code setting the reference to the new menu

'change to either

Set cbctrlCustom = cbWMB.Controls.Add(Type:=msoControlPopup, Before:=HelpMenu.Index + 1)
'or

Set cbctrlCustom = cbWMB.Controls.Add(msoControlPopup, , , HelpMenu.Index + 1)[/VBA] then I just changed your code to call one routine[VBA].OnAction = "WorkSheetRoutine"
'and here's the routine
Sub WorkSheetRoutine()
Worksheets(CommandBars.ActionControl.Caption).Activate
End Sub

[SJ McAbney]

Cheers. That solves it.