Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros
I received an intriguing request on Twitter from a follower named Brandi Leath, who asked how to customize the right-click menu to list and run a workbook’s macros. For example, a shortcut to see your macros listed, and to run a macro when you click its name, might look like this:
The first order of business is to do some housekeeping. Open your workbook and establish a reference in the VBE to Microsoft Visual Basic for Applications Extensibility 5.3:
Tools > References > Microsoft Visual Basic for Applications Extensibility 5.3 > OK
Next, you need to set Trusted Access for your VBA projects. The steps to do this in Excel versions 2003 and before are different than the steps in versions 2007 and after. See the following pictures to step you through the process depending on which version of Excel you are using.
Trusted Access to the VBA Project, Versions 2003 and before:
Tools > Macro > Security > Trusted Publishers tab > Trust access to Visual Basic Project > OK
Trusted Access to the VBA Project, Versions 2007 and after:
Developer tab > Macro Security icon > Macro Settings > Trust access to the VBA project… > OK
When building this code, three convenience items were considered:
(1) Place the custom item at the top of the right-click menu, so it is easily seen.
(2) Place a BeginGroup line below the custom item to visually separate it from other menu items.
(3) For user friendliness, show a reminder tip of this functionality when the workbook opens:
Regarding the code itself, the list of macros that would appear in the right-click menu must not include the names of macros that create the custom menu, nor any procedures that may exist in the workbook module, or any modules belonging to a worksheet, UserForm, or class.
In the downloadable workbook example are three macros for demonstration:
Sub Macro1() MsgBox "This is Macro1.", 64, "Test 1" End Sub Private Sub Macro2() MsgBox "This is Macro2.", 64, "Test 2" End Sub Sub Macro3() MsgBox "This is Macro3.", 64, "Test 3" End Sub
In the workbook module are these procedures to build and delete the custom menu:
Private Sub Workbook_Open() MsgBox "You can right-click any worksheet cell" & vbCrLf & _ "to see and / or run your workbook's macros.", 64, "A tip:" Run "RightClickReset" Run "MakeMenu" End Sub Private Sub Workbook_Activate() Run "RightClickReset" Run "MakeMenu" End Sub Private Sub Workbook_Deactivate() Run "RightClickReset" End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "RightClickReset" ThisWorkbook.Save End Sub
Finally, these macros build the custom right-click menu, list the macros, and identify and run a macro if its name is selected in the custom flyout menu:
Private Sub RightClickReset() On Error Resume Next CommandBars("Cell").Controls("Macro List").Delete Err.Clear CommandBars("Cell").Reset End Sub Private Sub MakeMenu() Run "RightClickReset" Dim strMacroName$, strLine$ Dim objCntr As CommandBarControl, objBtn As CommandBarButton Dim intLine%, intArgumentStart%, objComponent As Object Set objCntr = _ Application.CommandBars("Cell").Controls.Add(msoControlPopup, before:=1) objCntr.Caption = "Macro List" Application.CommandBars("Cell").Controls(2).BeginGroup = True For Each objComponent In ActiveWorkbook.VBProject.VBComponents If objComponent.Type = 1 Then For intLine = 1 To objComponent.CodeModule.CountOfLines strLine = objComponent.CodeModule.Lines(intLine, 1) strLine = Trim$(strLine) If Left$(strLine, 3) = "Sub" Or Left$(strLine, 11) = "Private Sub" Then intArgumentStart = InStr(strLine, "()") If intArgumentStart > 0 Then If Left$(strLine, 3) = "Sub" Then strMacroName = Trim(Mid$(strLine, 4, intArgumentStart - 4)) Else strMacroName = Trim(Mid$(strLine, 12, intArgumentStart - 12)) End If If strMacroName <> "RightClickReset" And strMacroName <> "MakeMenu" Then If strMacroName <> "MacroChosen" Then Set objBtn = objCntr.Controls.Add With objBtn .Caption = strMacroName .Style = msoButtonIconAndCaption .OnAction = "MacroChosen" .FaceId = 643 End With End If End If End If End If Next intLine End If Next objComponent End Sub Private Sub MacroChosen() With Application Run .CommandBars("Cell").Controls(1).Controls(.Caller(1)).Caption End With End Sub
The installed code will be functional after you close the workbook and reopen it, or after you activate a different workbook in that same instance of Excel and reactivate this workbook.
Thanks to Brandi Leath for asking the question.
Here again is the link to download the workbook example.