Add a Custom Context Menu in Excel with VBA

Once you have a working custom Microsoft Office ribbon tab, adding context menu items is easy. In this post, I will be demonstrating this process using code from my Excel workbook, the BidSmart Estimator. Code examples for setting up your custom ribbon tab can be seen in the VBA and XML of my Total Cost Model, which you can download here.

What do you want, or what do you need?

Custom context menu from The BidSmart Estimator

As with any programming task, it is always best to outline or at least fully understand what you want to accomplish. The examples shown here are somewhat complex, and some refer to several subroutines to achieve their objective. You may also want to look at my post which demonstrates the use of the Office RibbonX editor. And, reviewing this Microsoft webpage regarding Dynamic Menus may help. Though I find it a bit overwhelming, and it may be a rather tough read for your first look at this task

* Remember, this post assumes that you have a functional Custom Ribbon tab.

Organizing your code…

With any programming project, it is always best to create an organized system of modules, functions, or routines. For small projects you may balk, however, many small projects either become or are consumed by larger ones. When dealing with complexity, structure is your friend. The following image shows how I have organized my VBA code in the BS Estimator

Modules used by the BS Estimator

You will notice that I have 6 modules, each one with its own distinct purpose. This workbook has over 20 thousand lines of VBA code, so this setup makes my life much easier. Although the menu commands often refer to subroutines within the MainMod, the menu code is restricted to the modules, MenuMod and RibbonMod. A custom context menu will take several separate pieces of code just to have 1 menu item. With a larger menu, there will be many more sub-procedures and functions to track.

The code structure of the menu…

There are essentially three pieces of code needed to open and control a context menu item once you have a working custom ribbon tab:

  • The referral code in the ribbon XML(created and edited in the RibbonX editor.)
  • The code which defines the menu(s), located in this case in the MenuMod.
  • The subroutines which are fired by the menu items and do the actual work.

The dynamic (Context menu) menu’s XML is placed after the closing ribbon tag (</ribbon> and before the closing customUI tag </customUI>.) Here you create a section named <contextMenus> </contextMenus>. This is shown in the following screenshot of the RibbonX editor‘s display of the custom ribbon XML from my Estimator workbook.

Context menu XML – RibbonX editor

The ribbon XML

While the XML may look intimidating, it is actually very simple. Once you have some code written (or copied from another source) you can easily copy and paste to add more functionality. If you use the RibbonX editor, you can check the XML’s validity with the click of a button. Most problems are simple, but not always easy to find. The following code section shows an easier to read view of the previous image:

	</ribbon>

	<contextMenus>
		<contextMenu idMso="ContextMenuCell">
         		<dynamicMenu id="mnuCell" getLabel="getLabelCntxMnu" imageMso="CreateFormInDesignView" getContent="GetContent" insertBeforeMso="Cut"/>
      	</contextMenu>
		<contextMenu idMso="ContextMenuRow">
         		<dynamicMenu id="mnuRow" getLabel="getLabelCntxMnu" imageMso="CreateFormInDesignView" getContent="GetContent" insertBeforeMso="Cut"/>
      	</contextMenu>
		<contextMenu idMso="ContextMenuColumn">
         		<dynamicMenu id="mnuColumn" getLabel="getLabelCntxMnu" imageMso="CreateFormInDesignView" getContent="GetContent" insertBeforeMso="Cut"/>
      	</contextMenu>
		<contextMenu idMso="ContextMenuDesktop">
         		<dynamicMenu id="mnuDesktop" getLabel="getLabelCntxMnu" imageMso="CreateFormInDesignView" getContent="GetContent" insertBeforeMso="Cut"/>
      	</contextMenu>
		<contextMenu idMso="ContextMenuWorkbook">
         		<dynamicMenu id="mnuWorkbook" getLabel="getLabelCntxMnu" imageMso="CreateFormInDesignView" getContent="GetContent" insertBeforeMso="Cut"/>
      	</contextMenu>
		<contextMenu idMso="ContextMenuChartArea">
         		<dynamicMenu id="mnuChartArea" getLabel="getLabelCntxMnu" imageMso="CreateFormInDesignView" getContent="GetContent" insertBeforeMso="Cut"/>
      	</contextMenu>

   	</contextMenus>
</customUI>


The XML includes Elements that will need explaining. They are described at length in this Microsoft online documentation. My brief element breakdown is:

  • idMSO – Specifies the identifier of a built-in control and is not user defined. ContextMenuCell, for example, opens when you right-click on a cell.
  • dynamicMenu id – This is user defined. Create a unique name for this context menu.
  • label (getLabel) – The element, getLabel, specifies a connection to VBA code which will set the label based on criteria selected in a VBA procedure. I used getLabel so that I could change the menu “title” based on the worksheet from which it was called. A static label is fine with most applications.
  • imageMSO – The identifier of a built-in image that is used as the icon of this control. These images are stored within Office and can be found listed in this Microsoft document. There are numerous sites online listing these images which are more easily navigated, such as this webpage.
  • getContent – The name of a VBA Callback function to be called when the application needs to determine the contents of the control.
  • insertBeforeMSO – This is the placement of the context menu. In the first image, the “Estimator” context menu is placed above the Cut option, so the setting is insertBeforeMso=”Cut”. There are other elements for the menu location which are explained in the Microsoft documentation.

That’s all the XML needed to create your custom context menu. If you already had a working custom ribbon, all that remains is your VBA code within the document.

The VBA code

The following VBA code is placed in the MenuMod (as shown previously) module of the document, but it can be placed in any module that can be found by the running code. It is called by the attribute getContent=”GetContent” shown in the preceding code from the ribbon XML. You can name it whatever you wish, I simply was lazy and used “GetContent” for the name of the sub procedure. The GetContent sub supplies the code needed by the context menu to display. What follows is a short code snippet of the sub, GetContent, just to keep it simple.

Sub GetContent(control As IRibbonControl, ByRef returnedVal)
'**************************************************************************
'      Code for the dynamic context menus from the XML
'**************************************************************************

Dim xml As String

    Select Case ActiveSheet.CodeName
        Case "Sheet1"   ' BidSheet
            xml = "<menu xmlns=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
                "<button id=""mnuCtxt_Lookup"" label=""Search for an Keyword..."" imageMso=""FindDialog"" onAction=""mnuLookup""/>" & _
                "<button id=""mnuCtxt_QuickSum"" label=""Quick Sum"" imageMso=""AutoSum"" onAction=""mnuQuickSum""/>" & _
                "<button id=""mnuCtxt_Calendar"" label=""Show Calendar..."" imageMso=""CalendarInsert"" onAction=""mnuCalendar""/>" & _
                "<button id=""mnuCtxt_Envelope"" label=""Print an Envelope"" imageMso=""EnvelopesAndLabels"" onAction=""mnuEnvelope""/>" & _
                "<button id=""mnuCtxt_addNote"" label=""Add or Edit notes..."" imageMso=""GroupChangesTracking"" onAction=""mnuAddNotes""/>" & _
                "<button id=""mnuCtxt_Color_Me"" getLabel=""getLabelShading"" imageMso=""CellFillColorPicker"" onAction=""mnuColor_Me""/>" & _
                "<button id=""mnuCtxt_Indent_Me"" label=""Quick Indent"" imageMso=""AlignRight"" onAction=""mnuIndent_Me""/>" & _
                "<button id=""mnuCtxt_Remove_Blk"" getLabel=""getLabelRemoveLines"" imageMso=""SheetRowsDelete"" onAction=""mnuRemove_Blk""/>" & _
                "<button id=""mnuCtxt_StrikeThru"" getLabel=""getLabelStrikeThru"" imageMso=""Strikethrough"" onAction=""mnuStrikeThru""/>" & _
                "<button id=""mnuCtxt_RemoveSubs"" label=""Remove Subtotals"" imageMso=""PivotRemoveField"" onAction=""mnuRemoveSubTotals""/>" & _
                "<button id=""mnuCtxt_ClearValues"" label=""Clear the numerical input"" imageMso=""ClearAll"" onAction=""mnuClearValues""/>" & _
                "<button id=""mnuCtxt_Refresh_Ribbon"" label=""Refresh the Excel ribbon..."" imageMso=""AccessRefreshAllLists"" onAction=""mnuRefresh_Ribbon""/>" & _
                "<menuSeparator id= ""separator1""/>" & _
                "<button id=""mnuDataEntry"" label=""Data Entry form..."" imageMso=""AccessFormModalDialog"" onAction=""mnuShowDataEntry""/>" & _
                "<button id=""mnuCtxt_ContractInfo"" label=""Edit Contract and Client Info..."" imageMso=""FilePrepareMenu"" onAction=""mnuContractInfo""/>" & _
                "<button id=""mnuCtxt_WhatIf"" label=""What-If Analysis..."" imageMso=""WhatIfAnalysisMenu"" onAction=""mnuWhatIf""/>" & _
                "<button id=""mnuCtxt_Password"" label=""Manage the Password..."" imageMso=""AdpDiagramKeys"" onAction=""mnuPassword""/>" & _
                "<button id=""mnuCtxt_ManageDB"" getLabel=""getLabelDB"" getImage=""getImageDB"" onAction=""mnuManageDB""/>" & _
                "</menu>"

' more code is actually here, but is left out to keep this example short

    End Select
        
    returnedVal = xml
    
End Sub

You will notice that all the sub does is construct a string (Dim xml As String) which will be read as XML by the ribbon XML. The elements in this code are all user-defined. Some refer to VBA code which must exist for the menu to work. The returnedVal at the end is XML used by the ribbon…remember that the previous code is not complete. For this code to work, you need a valid XML string returned. If the finished context menu does not load or does nothing, then there is often a problem with this code. The unfortunate part is that all the XML troubleshooting of the string must be done manually, looking for missing terms, spaces, misspellings, etc.

The basic elements of the VBA created XML string are:

  • button id – A user defined button name, used for your own reference.
  • label – This is the label that appears on the menu, such as the standard labels, Cut, Copy and Paste. Yours, of course, should not mimic any standard labels. It will certainly confuse your users.
  • imageMso – This specifies the Office control image that will be shown. If left out, there will be no image displayed. Which is what you may want.
  • menuSeparator id – Adds a line to separate distinct menu options (See the first screenshot.)
  • screentip – Does not seem to work for the context menus.
  • onAction – This element indentifies the VBA “Callback” procedure. This is the code that does what the menu item is meant to do.

The action code, the point of the menu…

All that remains at this point is to code the onAction routines. The last piece of code shows what happens when you select the “Search for an Keyword…” context menu item. The sub needs to be written using the “control As IRibbonControl” argument as shown to work.

Public Sub mnuLookup(control As IRibbonControl)
' Callback for mnuCtxt_Lookup Context menu onAction

    Call Lookup
    
End Sub

Additional VBA code which may be required

The mnuLookup sub calls another more lengthy sub which is shared by other procedures throughout the BidSmart Estimator. So more code may be needed in addition to the CallBack routines in man cases.

That’s really all there is to it.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.