Back
Excel

Creating a Custom Popup Menu

Today’s author is Derek Mang, owner of Systems Solution Developers Inc. You can find more useful tips from Derek on his website: www.officevbasolutions.com.

This post shows you how to create a custom popup menu in Excel.

Microsoft Excel has lots of popup menus that are made available to the user with a right-click depending on what your doing – hence the term context menu. Right-click on a cell and you get the Excel “cell” popup menu and its available choices. This menu is subject to customization, allowing you to add items to the menu or disable menu items if desired.

Depending on your needs, a dropdown menu could get to be awkwardly large. Furthermore, you get the same menu for each cell, and if there are dependencies on cell content you may wind up with too many choices. A completely custom menu based on cell content or range, would better satisfy specific needs.

I found an example of the custom menu on the net and extended this using a worksheet class module to include all worksheets in a workbook rather than just one as found in the original example.

The following code will create and popup with three custom menus (Red, Yellow and Green) on any worksheet in the current workbook where the right-clicked cell is a shade of red, yellow or green respectively.

The workbook requires code in the Workbook.Open event, as well as one code module, and one class module.

The key is in the class module. The class module contains handlers for worksheet events that are fired whenever an action occurs on (in this case) any worksheet in the workbook. Of particular interest is the Worksheet.BeforeRightClick event. As the event name suggests, “what do you want to do before the default action that occurs when a user right-clicks in a worksheet.†This opens up all kinds of possibilities all based on the cell that has been right-clicked by the user.

In this sample, the Range.Interior property is used to access the cell’s (aka target) Interior.ColorIndex property, and then depending on the value for color returned, the default popup menu is cancelled and corresponding custom popup menu is shown depending on the property value returned.

Since discovering this technique, I have included it – and it has been well-received – in several custom Excel solutions to limit end-user activity to specific tasks.

Workbook_Open

The code for the Workbook.Open event handler establishes the three popup menus, and sets up the worksheet objects in the class. Open the Visual Basic Editor and paste this code into the ThisWorkbook module of an Excel Macro-Enabled Workbook (.xlsm) to define the event handler for the Workbook.Open event.

Private Sub Workbook_Open()
    Set cb_Red = CreateSubMenu("Red")
    Set cb_Yellow = CreateSubMenu("Yellow")
    Set cb_Green = CreateSubMenu("Green")
    Call SetupAllWSEvents
End Sub 

Code Module

The code module contains the class set up and the actual menu creation routines. In the Visual Basic Editor, click Module on the Insert menu, and then paste this code into the module.

Option Explicit
Global cb_Red As CommandBar
Global cb_Yellow As CommandBar
Global cb_Green As CommandBar
Global WSObj As Collection
Global ws As Worksheet
Sub SetupAllWSEvents()

    Dim WSo As clsWS
    Set WSObj = Nothing
    Set WSObj = New Collection
    For Each ws In ActiveWorkbook.Worksheets
        Set WSo = New clsWS
        Set WSo.WSToMonitor = ws
        WSObj.Add WSo, ws.Name
    Next ws
    
End Sub

Function CreateSubMenu(strCB) As CommandBar

    Const CBPREFIX = "CustomPopUp"
    Dim cb As CommandBar
    Dim cbc As CommandBarControl
    Dim strCBName As String
    'custom menu name
    strCBName = CBPREFIX & strCB
    'remove previous instance
    Call DeleteCommandBar(strCBName)
   'Add our popup menu to the CommandBars collection
    Set cb = CommandBars.Add(Name:=strCBName, _
        Position:=msoBarPopup, _
        MenuBar:=False, _
        Temporary:=False)
    'Add controls
    Set cbc = cb.Controls.Add
    With cbc
        .Caption = strCB & " &Control 1"
        .OnAction = "DummyMessage"
    End With
    
    Set cbc = cb.Controls.Add
    With cbc
        .Caption = strCB & " Control &2"
        .OnAction = "DummyMessage"
    End With
    
    Set CreateSubMenu = cb
    Set cbc = Nothing
    Set cb = Nothing
    
End Function
Sub DeleteCommandBar(cbName)
    
    On Error Resume Next
    CommandBars(cbName).Delete
    
End Sub
Sub DummyMessage()
    MsgBox CommandBars.ActionControl.Caption, vbInformation + vbOKOnly, "Dummy Message"
End Sub

Class Module

The class module determines which menu to “popup” depending on characteristics of the target cell. In the Visual Basic Editor, click Class Module on the Insert menu, change the name of the class module to clsWS, and then paste this code into the module. (Be sure to change the name to clsWS so that it will be available as the class of that name for the SetupAllWSEvents() subroutine.)

Dim WithEvents aWS As Worksheet

Property Set WSToMonitor(uWS As Worksheet)
    Set aWS = uWS
End Property

Private Sub aWS_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Select Case Target.Interior.ColorIndex
        Case 3, 9
        cb_Red.ShowPopup
        Cancel = True 'defeat the standard cell popup menu
    Case 4, 10, 35, 43, 50, 51, 52
        cb_Green.ShowPopup
        Cancel = True
    Case 6, 12, 36, 44
        cb_Yellow.ShowPopup
        Cancel = True
    Case Else
        Cancel = False
    End Select
End Sub

To demo the code, set the fill color of several cells to shades of red, yellow, and green, close the workbook, and then re-open it to run the Workbook_Open() code that sets up the popup menus. When you right-click the colored cells, the popup menus should look like the following examples.

clip_image002 clip_image002[4] clip_image002[6]

Acknowledgements

The original sample that provided inspiration and insight was authored by Andy Wiggins (Byg Software)