Creating Popup Menus in All Versions of Excel

  • Article

Summary:   See how to create popup menus that work in all versions of Microsoft Excel.

Applies to: Excel | Excel 2010 | Office 2007 | Office 2010 | SharePoint Server 2010 | VBA

Published:   April 2011

Contents

  • Introduction to Popup Menus

  • Creating Popup Menus

  • Displaying the Popup Menu

  • Conclusion

  • Additional Resources

Introduction to Popup Menus

A popup menu (also sometimes called contextual or shortcut menus) is a menu in a user interface (UI) that appears from some user action, such as a right mouse click. A popup menu offers a limited set of choices that are available in the current state, or context, of the application. Popup menus may consist of a series of single commands, a listing of nested commands, or usually as a combination of both. Popup menus are available in all Microsoft Office programs. How popup menus are created varies depending on the version of the Microsoft Office program such as Microsoft Excel, Microsoft Outlook, and Microsoft Word.

Most recently, the technique for creating menus that work in different versions of Microsoft Office is different primarily because in the 2007 Microsoft Office system, Microsoft replaced the Command Bar menu structure with the ribbon UI. So in Microsoft Office Excel 2007, for example, the Worksheet Menu Command Bar seen in previous versions of Excel has been replaced with similar commands on the ribbon UI. This difference in UI means that, for developers, creating a menu structure that works across all versions of Microsoft Office is more challenging. However, as you will see in this article, one technique that works involves using a Command Bar popup menu.

Creating Popup Menus

In this section, you create a popup menu in Excel. Note that one way that built-in or custom context menus differ from popup menus is that context menus are only displayed when you right-click the mouse. Popup menus on the other hand can be displayed when you want them to be.

To create a popup menu in Excel

  1. Open a new workbook.

  2. Save the file by using either a .xls or .xlsm (macro-enabled file in Excel 2007 and Excel 2010) file name extension depending on the version of Excel you are using.

  3. Press Alt+F11 to open the Visual Basic Editor.

  4. On the Insert menu, click Module to create a module to contain your code.

  5. Type or paste the following code into Module1.

    Option Explicit

Public Const Mname As String = "MyPopUpMenu"

Sub DeletePopUpMenu()
    ' Delete the popup menu if it already exists.
    On Error Resume Next
    Application.CommandBars(Mname).Delete
    On Error GoTo 0
End Sub

Sub CreateDisplayPopUpMenu()
    ' Delete any existing popup menu.
    Call DeletePopUpMenu

    ' Create the popup menu.
    Call Custom_PopUpMenu_1

    ' Display the popup menu.
    On Error Resume Next
    Application.CommandBars(Mname).ShowPopup
    On Error GoTo 0
End Sub

Sub Custom_PopUpMenu_1()
    Dim MenuItem As CommandBarPopup
    ' Add the popup menu.
    With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
         MenuBar:=False, Temporary:=True)

        ' First, add two buttons to the menu.
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 1"
            .FaceId = 71
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 2"
            .FaceId = 72
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

        ' Next, add a menu that contains two buttons.
        Set MenuItem = .Controls.Add(Type:=msoControlPopup)
        With MenuItem
            .Caption = "My Special Menu"

            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Button 1 in menu"
                .FaceId = 71
                .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
            End With

            With .Controls.Add(Type:=msoControlButton)
                .Caption = "Button 2 in menu"
                .FaceId = 72
                .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
            End With
        End With

        ' Finally, add a single button.
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 3"
            .FaceId = 73
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

    End With
End Sub

Sub TestMacro()
    MsgBox "Hi there! Greetings from the Netherlands."
End Sub

  6. Press Alt+Q to close the Visual Basic Editor.

  7. Save the file.

    Note

    You can change the button images by changing the FaceId values. Find more information about this at Ron’s website.

Displaying the Popup Menu

In this section, you add code to the project to demonstrate different methods to display the popup menu.

Use a shortcut to display the popup menu

  1. Press Alt+F8 to open the macro dialog box.

  2. Select the CreateDisplayPopUpMenu macro.

  3. Click the Options button in the dialog box.

  4. Type the letter m, and then click OK.

  5. Close this dialog box and try the shortcut Ctrl m. You see the menu shown in Figure 1.

    Figure 1. The custom menu is displayed

    The custom menu is displayed

  6. Click Button 1. The dialog box shown in Figure 2 is displayed.

    Figure 2. Dialog box displayed from the popup menu

    Dialog box displayed from the popup menu

  7. Save the file.

Warning

It is possible that if you have another workbook open that uses the same shortcut as the one that you use here, you may not see the popup menu when you use the shortcut.

You can close the workbook without deleting the menu. This is not an issue because when the menu was created by using the With Application.CommandBars.Add method, its Temporary parameter is set to True. This automatically deletes the menu when you close Excel. However, it is always a good practice to explicitly delete the menu by using the following procedure.

To delete the menu

  1. Press Alt+F11 to open the Visual Basic Editor.

  2. Open the ThisWorkbook module.

  3. Paste this code into the module.

    Private Sub Workbook_Deactivate()
    Call DeletePopUpMenu
End Sub

  4. Press Alt+Q to close the Visual Basic Editor.

  5. Save the file.

In the following section, you add code to the project that uses workbook events to add a button to the Cell context menu which calls the popup menu. The Cell context menu is what you see when you right-click a cell or selection in a cell.

Note

It is also possible to add all of your menu items to a context menu. To learn more, see Customizing Context Menus in All Versions of Microsoft Excel.

To add a button to the Cell context menu

  1. Press Alt+F11 to open the Visual Basic Editor.

  2. In Module1, paste the following code below the existing code.

    Sub AddToCellMenu()
    Dim ContextMenu As CommandBar

    ' First, delete the control to avoid duplicates.
    Call DeleteFromCellMenu

    ' Set Context Menu variable to point to the Cell menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Add one custom button to the Cell menu.
    With ContextMenu.Controls.Add(Type:=msoControlButton, before:=1)
        .OnAction = "'" & ThisWorkbook.Name & "'!" & "CreateDisplayPopUpMenu"
        .FaceId = 59
        .Caption = "My Popup menu"
        .Tag = "My_Cell_Control_Tag"
    End With
End Sub

Sub DeleteFromCellMenu()
    Dim ContextMenu As CommandBar
    Dim ctrl As CommandBarControl

    ' Set ContextMenu variable to point to the Cell menu.
    Set ContextMenu = Application.CommandBars("Cell")

    ' Delete custom controls with the tag: My_Cell_Control_Tag.
    For Each ctrl In ContextMenu.Controls
        If ctrl.Tag = "My_Cell_Control_Tag" Then
            ctrl.Delete
        End If
    Next ctrl
End Sub

  3. Open the ThisWorkbook module.

  4. Type or paste the following code into the module. Replace the Deactivate event of the previous example with the following one, if it exists.

    Private Sub Workbook_Activate()
    Call AddToCellMenu
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteFromCellMenu
    Call DeletePopUpMenu
End Sub

  5. Press Alt+Q to return to the Excel window.

  6. Save the file, close, and then reopen it.

  7. Right-click any cell. You see the Cell context menu as shown in Figure 3.

    Figure 3. Customized Cell context menu

    Customized Cell context menu

  8. Click the top menu item to see the dialog box displayed as shown in Figure 2.

You may want to display a different menu for each sheet or only display the menu on a few sheets. You use the following procedures to do that.

To limit the scope of a Popup menu

  1. In the same workbook, press Alt F11 to open the Visual Basic Editor.

  2. In Module1, type or paste the following macro below the existing code.

    Sub Custom_PopUpMenu_2()
    ' Add popup menu with three buttons.
    With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
        MenuBar:=False, Temporary:=True)
        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 1"
            .FaceId = 71
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 2"
            .FaceId = 72
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With

        With .Controls.Add(Type:=msoControlButton)
            .Caption = "Button 3"
            .FaceId = 73
            .OnAction = "'" & ThisWorkbook.Name & "'!" & "TestMacro"
        End With
    End With
End Sub

  3. Next, still in Module1, replace the macro named CreateDisplayPopUpMenu with the following macro.

    Sub CreateDisplayPopUpMenu()
    ' Delete the popup menu if it exist.
    Call DeletePopUpMenu

    ' Create the correct menu based on the active worksheet.
    Select Case ActiveSheet.Name
    Case "Sheet1": Call Custom_PopUpMenu_1
    Case "Sheet2": Call Custom_PopUpMenu_2
    Case Else: MsgBox "Sorry no Popup Menu"
    End Select

    ' Show the popup menu.
    On Error Resume Next
    Application.CommandBars(Mname).ShowPopup
    On Error GoTo 0
End Sub

  4. Press Alt+Q to return to the Excel window and save the file.

  5. Now, try it out by pressing Ctrl m first in Sheet 1, then in Sheet2, and finally in Sheet3. Note that you may need to re-create the keyboard shortcut combination (Ctrl m) by using the procedures that are described at the beginning of this article. You can also test this out by right-clicking a cell and then using the Cell menu in Sheet1, then in Sheet2, and finally in Sheet3.

When Sheet1 is active, the popup menu with 3 buttons and another menu with 2 buttons will be displayed. And when Sheet2 is active, the popup menu with 3 buttons will be displayed. If any other worksheet is active, the Sorry no Popup Menu dialog box appears. This gives you full control of the popup menu options available on each worksheet.

In the following steps, you add a button to the Quick Access Toolbar in Excel 2007 and Excel 2010 that displays a popup menu.

To add a menu button to the Quick Access Toolbar

  1. Open the workbook from the previous examples and make sure that you save it with the .xlsm (macro-enabled file) file name extension.

  2. Right click on the Quick Access Toolbar and select Customize Quick Access Toolbar.

  3. In the Choose commands from drop-down list, select Macros.

  4. In the Customize Quick Access Toolbar drop-down list, select For <yourworkbookname>.xlsm.

  5. Select the CreateDisplayPopUpMenu macro, click Add, and then click OK. This displays the button shown in Figure 4 on the Quick Access Toolbar. You can use Modify in the customize Quick Access Toolbar window to change the icon, if you want.

    Figure 4. New button added to the Quick Access Toolbar

    New button added to the QAT

  6. Finally, click OK and save the file.

  7. Click the button on the Quick Access Toolbar to display the menu.

Note

You only have to do this one time because the button is saved with the workbook. If you send the file to other users, they can use your button on the Quick Access Toolbar.

Conclusion

Popup menus give you more flexibility in the options that you can offer to your users than built-in or custom context menus. Popup menus can be attached to events, to existing menus, or to the Quick Access Toolbar. You should continue to experiment with the methods discussed in this article to see how you can add versatility in your own application.

Additional Resources

Find more information about the topics discussed in this article at the following locations: