Tom’s Tutorials For Excel: Customizing Your RightClick Menu to List & Run Macros
Download this example of a custom right-click menu to list and run your 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.
That is exactly what I needed!!! Thank you so much!!!
You’re welcome Brandi, thanks for the question.
Now I have another question. I see this is for a single workbook. Is it possible to put this into my Personal Macro Workbook so I can run this macro on every workbook I create?
I never use the Personal Macro Workbook, just never liked it. Instead, to make this available to all your workbooks, I’d suggest implementing it as an add-in.
Also my macros aren’t showing up on the menu unless I close the workbook and reopen it. Thoughts?
Take a look at what I wrote near the very bottom of my post:
“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.”
It is event code, so an event needs to take place as a one-time deal after the code is first installed. One event is to close and reopen the workbook, and another event is to activate another workbook (or hit Ctrl+N to create one) and re-activate the workbook you just put the code into. After that, the code will always be available whenever you use that workbook.
Hi Tom,
You might want to add some more code to cater for valid use of () in the signature of subs to specify arrays.
eg.
Sub TestArrayArg(Index As String, MyArray() As Variant)
End Sub
Sub TestParamArray(ParamArray Args())
End Sub
Hi Andy, excellent tip & idea, thanks.
Thanks for the tip – very helpful. How can I put Subs from a particular module (only) on this right-click list? What about two Macro Lists that appear upon right-clicking, such as:
Right Click:
Macro List 1 (shows Subs from Module X only)
Macro List 2 (shows Subs from Moduly Y only)
thanks!
Hi Pat, thanks for the question.
What you asked about is do-able but would require a bit more programming than the scope of this example intended. It can easily get to a development project with such items to address as:
• How would you determine which subs from a particular module should and shouldnot be included.
• Why are the macros divided among the modules, any theme?
• What if theer are 3 or 8 or 20 modules, is this supposed to be dynamic?
There are other considerations as well, these are just off the top of my head. But maybe tell me if this is for just you, or other users too, which might be an issue with varying local settings. Finally, if this is a company project and your employer wants this level of detail, they may want other features added too and we can talk abouta development project, *if* that’s what this would end up being.
Thanks again for visiting my blog, and for your question!
Tom
Hi
I am having an issue wherein the Atlas menu is not coming on to the Excel application, Even though the Atlas client shows successful log in message. This happened suddenly as the Atlas menu was showing until this event.
Thanks in advance
Don’t know what you mean by “Atlas menu”. Everything worked when I tested it on any Excel version.
Hi Tom,
Great template for all kinds of additions to my right click!
Had to make a small adjustment before it would work as an add-in:
Replaced “ActiveWorkbook” to “ThisWorkbook”.in MakeMenu
Thanks again,
Anil
Thank you, Anil !!
Well documented and useful. Thank you!
Question – When using code in one of my spreadsheets, the right-click macro list shows up in any cell I right-click except within a table (which is where I need it to show up). Any thoughts/ideas are greatly appreciated.
Thanks for following my Excel blog page. Can you please help me understand what you mean by “table”. Is it a pivot table, or is it a list of data (maybe a few columns wide) that you have designated as a Table maybe in 2010 or 2013? It might be that the properties of your table do not allow for the right click menu to work. Off the top of my head I don’t see why it wouldn’t, but trust me, I learn more from my Excel followers and trainees than from the Excel books I read and write. I wouldn’t be surprised if you pointed out something I had not seen or thought of regarding this right click event code. Please tell me the version, operating system, and bit (32 or 64) you are using, is it on a Mac, any merged cells, or any other info you can throw in there to help me get a handle on what you’re seeing. Thanks a lot, really appreciate your input on this so I can adjust the code if needed.
When working within tables, you need to use .CommandBars(“List Range Popup”) instead of .CommadBars(“Cell”). So you need to test the target for ListObject is Not Nothing and use the former when manipulating the menu.
Thanks for information.
I made template this topic and I added a date userform to right-click(context) menu.In this way you can add “date” with right-click menu to selected cell.
Link : https://netmerkez.wordpress.com/excel/add-right-click-menu
Hi Tom.
I purchased Excel VBA 24-Hour Trainer (2nd Edition) and downloaded the workbook for lesson 2. I am running Windows 7 with MS Office Pro 2013. When I open the workbook, the flyout macro menu you created doesn’t work. Run-time error ‘1004’. Cannot run the macro ‘MakeMenu’.
Any thoughts on how to overcome this?
Thank you,
Kenda
Hi Kenda —
Thanks for your message. I just replied to your email address, with an original, working copy for you of that downloadable file.
Tom
Hi Tom,
How could I edit the code so that the Macro sub-menu will only be displayed / available when I select any of the cells under the A Column?
Furthermore: I´d like the sub menu would only be displayed when a visible cell is selected (assuming that there is an active filter in the A Column )
Greetings from Germany and thanks for this very good tutorial,
Ruben
Thanks for your kind words about the tutorial, Ruben.
Regarding your question, I’m assuming that you only want the submenu to be available for column A of a specific worksheet. Supposing it is Sheet1, then in the workbook module’s Open and Activate events, you can insert this line as the very first line of those events:
If ActiveSheet.Name <> “Sheet1” then Exit Sub
Then, in the worksheet module for Sheet1, insert 3 events. One of those 3 events would be the Activate event where you can use the Run statement to reinstate the right click menu especially for that worksheet.
The second event in that worksheet module would be the SelectionChange event, to build or delete the submenu depending on if a cell in column A has been selected or some other cell on that worksheet outside of column A has been selected.
The third event in the worksheet module would be the Deselect event, which would hold the Run statement for the macro that deletes the menu, so the selection of some other worksheet will result in that submenu not being available.
As to the question about not triggering the code when a hidden cell is selected due to filtering or the row simply being hidden, the SelectionChange event would automatically handle that because a hidden cell wouldn’t be selected. If it were me building the project for a client, I’d take into consideration that yes, a non-visible can be selected programmatically or using the name box, so I’d add another layer of protection for that in the SelectionChange event to only run the menu building macro if the active cell’s row (for the cell which just got selected) is in a visible or hidden state.
Hello,
in case of rightclick on a table (List range popup bar), do you know how we can adapt the rightclick menu following the line or the column on which we click, please?
For example, the menu per default gives more commands of insertion of line or column if we rightclick on the last cell (on the last line and the last column) of the table…
Thank you in advance.
Yohann
Hi Tom,
Hope you are still keeping an eye on your excellent tutorials.
I found your blog in looking for a problem I have running an Excel file that used to work fine in Windows 7-64bit. It won’t run on my Windows 10-64bit system using Microsoft Office Home and Business 2010. It included many right-click context menus, none of which work now.
It needed an ocx file (mscomct2.ocx which I have added to the references) to function properly because of some legacy programming.
I can’t get your RightClickMacroMenu to work either.
It comes up with an “Object variable or With block variable not set.” error 91 problem in the Sub MakeMenu().
I’ve allowed Macros and set the Macro Security to trust access, etc.
Any ideas as to why it won’t work in Windows 10?
Regards, Hans
Hello Hans – –
The workbook works just fine for me on my 2016 system on Windows 10, 64-bit. Actually, it turns out that this is not a 64-bit issue.
So, try opening the workbook again. Go into the VBE, click on Tools > References, and…
• establish a reference to Microsoft Visual Basic For Applications 5.3
and
• establish a reference to Microsoft Forms 2.0 Object Library.
Go back to the worksheet, save the workbook, close the workbook, and reopen it, and you should be good to go. Post back if you still have problems. I just tested it again with these steps and it operated thereafter without any problems.
Excel how do I add print command on the right click mouse button
In the workbook module:
Private Sub Workbook_Open()Run "MakeMenu"
End Sub
Private Sub Workbook_Activate()
Run "MakeMenu"
End Sub
Private Sub Workbook_Deactivate()
Run "RightClickReset"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "RightClickReset"
End Sub
In a standard module:
Private Sub RightClickReset()On Error Resume Next
CommandBars("Cell").Controls("My Print").Delete
Err.Clear
CommandBars("Cell").Reset
End Sub
Private Sub MakeMenu()
Run "RightClickReset"
Dim cb As CommandBar, MenuObject1 As CommandBarButton
Set cb = Application.CommandBars("Cell")
Set MenuObject1 = _
cb.Controls.Add(Type:=msoControlButton, before:=1)
With MenuObject1
.Caption = "My Print"
.OnAction = "myPrintMacro"
End With
Application.CommandBars("Cell").Controls(2).BeginGroup = True
Set cb = Nothing
Set MenuObject1 = Nothing
End Sub
Private Sub myPrintMacro
MsgBox "Put your actual print code here.", , "Example"
End Sub
When I select excel by rows, the custom menu is not available anymore, is there other syntax for entire row selection VS single/range cells selection?
I have not tested this, but try putting this into your workbook module along with the other code I posted:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Selection.Cells.Count = Rows(Target.Row).Cells.Count Then
Run "RightClickReset"
Run "MakeMenu"
End If
End Sub