Making a menu that is working in every Excel version is not so easy anymore because in Excel 2007 Microsoft replaced the Worksheet Menu Bar with the Ribbon. One thing that is working in almost every Excel version is to add menu items to a Context Menu, for more information visit:
Customizing Context Menus in Microsoft Excel

Another way that will work in every Excel version is to create a PopUp menu like I show you on this page.

Summary: Learn how to create a PopMenu that is working in every Excel version
Note: You can find this info also in my MSDN article: http://msdn.microsoft.com/en-us/library/gg987030.aspx

Overview of PopUp menus in Excel
Making a PopUp menu is almost the same as making a Context Menu, see this article for more information about Context Menus. The big differents is that Context Menus are only visible when you right click for
example on a cell/range or a specific object and Popup menus are visible when you want it.
To display a PopUp menu that you have created with VBA code you must use a code line like this to let it popup Application.CommandBars(Popup name).ShowPopup


Let's start

1: Open a new workbook
2: Save the file with a nice name, save as xls or xlsm(Macro enabled workbook in Excel 2007-2010)
3: Press Alt-F11 to open the VBA editor
4: Use Insert>Module in the menubar to create a Module to store the code
5: paste the code below in the module (note it is possible that Option Explicit is already in the module)

Option Explicit

Public Const Mname As String = "MyPopUpMenu"

Sub DeletePopUpMenu()
    'Delete PopUp menu if it exist
    On Error Resume Next
    Application.CommandBars(Mname).Delete
    On Error GoTo 0
End Sub

Sub CreateDisplayPopUpMenu()
    'Delete PopUp menu if it exist
    Call DeletePopUpMenu

    'Create the PopUpmenu
    Call Custom_PopUpMenu_1

    'Show 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 PopUp menu
    With Application.CommandBars.Add(Name:=Mname, Position:=msoBarPopup, _
                                     MenuBar:=False, Temporary:=True)

        'First add two buttons
        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

        'Second Add menu with 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

        'Third add one 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: Alt q to close the VBA editor
7: Save the file

Now we have code in the file that create a PopUp menu with a few buttons and a menu with two buttons
but how do we display this PopUp menu when we want it ?
Note: for testing every button option call the same macro.


Use a shortcut to call the menu

1: Alt F8 to open the macro dialog
2: Select the CreateDisplayPopUpMenu macro
3: Click on the Options button in the dialog
4: Insert the letter m for example and press OK
5:Close this dialog and try the shortcut Ctrl m
6: If correct you see the menu



7: Save the file

Note: if another workbook that is opened after this workbook use the same shortcut
this workbooks shortcut will be used, the last workbook win the game.
This shortcut will also work if another workbook is active.

When we close the workbook we not delete the menu now. This is not a big deal because we create the
menu with the last argument Temporary to True so it will automaticly deleted when you close Excel.
but it is good practice to delete it in the Workbook_Deactivate event of the workbook.

1: Alt F11 to open the VBA Editor
2: Go to the ThisWorkbook module
3: Paste the code below in this module

Private Sub Workbook_Deactivate()
    Call DeletePopUpMenu
End Sub

4: Alt q to close the VBA editor
5: Save the file



Use Events to create a button in the Cell context menu to call the PopUp menu

Note: The cell menu is the context menu that you see when you right click on a cell or selection
Before we go try this example we save the file with a different name so we leave the first example untouched.

1: Alt F11 to open the VBA Editor
2: Go to the module with the code
3: Paste the code below in the module below the other code

Sub AddToCellMenu()
    Dim ContextMenu As CommandBar

    'Delete the control first to avoid duplicates
    Call DeleteFromCellMenu

    'Set ContextMenu 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 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

4: Go to the ThisWorkbook module
5: Paste the code below in this module (replace the Deactivate event of the example above with the one below)

Private Sub Workbook_Activate()
    Call AddToCellMenu
End Sub

Private Sub Workbook_Deactivate()
    Call DeleteFromCellMenu
    Call DeletePopUpMenu
End Sub

6: Alt q to go to Excel
7: Save the file and close and reopen

If correct you can call your PopUp now with the menu option on top of the Cell menu.
Screenshot below is from the Excel 2010 cell menu





Tip: limit a PopUp menu to a single worksheet within a single workbook

What if you want to display a different menu for each sheet or only display the menu on a few sheets ?

Open the workbook from the example above and add the code below to this workbook.

1: Alt F11 to open the VBA Editor
2: Go to the module with the code
3: Paste the macro below in the module below the other 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

Then replace the macro named CreateDisplayPopUpMenu with the macro below

Sub CreateDisplayPopUpMenu()
    'Delete 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

When worksheet Sheet1 is active it will open the PopUp menu with 3 buttons and a Menu with 2 buttons.
And when worksheet Sheet2 is active it will open a PopUp menu with 3 buttons.
If any other worksheet is active no PopUp menu will be displayed.
This way you have full control of what your users menu options are on each worksheet.




Add button to call the PopUp menu to the QAT in Excel 2007-2010

In the example above we have add a button to call the menu in the Cell context menu but if you use
Excel 2007 or Excel 2010 it is also possible to add a button to the QAT.

Open the workbook from the first example and be sure you save it as xlsm(macro enabled file)

Right click on the QAT and choose Customize Quick Access Toolbar
In the “Choose commands from” dropdown choose "Macros" and in the Customize Quick Access Toolbar dropdown choose "For YourWorkbookName.xlsm"

Select the CreateDisplayPopUpMenu macro and press Add and then OK.
You can use Modify to change the icon if you want, I use the green button as you see.
Then press OK and save the file.

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 QAT.

If you do it correct it looks like this




Note: For another easy PopUp menu example for Excel 2007-2010 visit the pages below :

Menu for favorite macros in Excel 2007-2010 (for all workbooks)
http://www.rondebruin.nl/qat.htm

Menu for favorite macros in Excel 2007-2010 (for one workbook)
http://www.rondebruin.nl/qat2.htm



Button Images (FaceId's)

You see that I use FaceId 71 for a image with the number 1 and FaceId 59 for the smiley in the examples,
but how do i know which FaceId numbers I can use?
The files below that can help you with that are created for Excel 97-2003, so it is easier to find the FaceId numbers that you want to use when you use the files in these versions.

Use the BtnFace add-in from Jim Rech to find every FaceId
Download it from my site

Check out this page from John Walkenbach to find every FaceId
http://spreadsheetpage.com/index.php/tip/identifying_commandbar_images/