Make your own popup menu in Excel with VBA macros

With VBA you can easily make a popup menu (shortcut menu) that pops up, when you right-click, and replaces Excel's built-in menu for right-click.

Each menu item can call a macro or a standard function in Excel. It can be hidden, shown, disabled or enabled runtime dictated by circumstance, i.e. the value of a variable can determine the status of a menu item.

It requires only two macros to make the popup menu appear. One, where you design the menu, and one that calls the menu, when you right-click.

However it is pratical to make the menu automatically, when the spreadsheet is opened - more about this later.

To test the example below, copy the macros "CreateShortcut" and "Dummy1" to "Dummy7" into a standard module in VBA. Just hightlight the code with the mouse, copy (CTRL+C) and paste (CTRL+V). If you use a small screen, some code lines may appear broken, but they will be okay when pasted.

The macro "Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)" must be copied to the code sheet for the worksheet (or worksheets) where you want to use your own popup menu.

In the image to the right the code sheet for Sheet1 has been selected. Here you can put the kind of code that executes automatically, when certain events happen on this particular worksheet.

Another option is to download a spreadsheet (Excel 2003) with the demo menu. The spreadsheet is zipped, and you unzip it by right-clicking and doing whatever Windows suggests.

If you want your popup menu to work automatically, when you open the spreadsheet, you can make an Auto_Open procedure like this:

Sub Auto_Open()
   CreateShortcut
End Sub

The procedure executes automatically, when the spreadsheet is opened. And now back to business - first the procedure that creates the menu, and then the 7 demo macros that just pop up with a message. Finally the macro that must be copied to the worksheet's code sheet.

Sub CreateShortcut()
Dim myBar As CommandBar
Dim myItem As CommandBarControl

On Error Resume Next

'Deletes any old popup menu.
CommandBars("MyShortcut").Delete

On Error GoTo ErrorHandle

'We now add our 'command bar' to Excel's colllection
'of command bars and name it "MyShortcut".
Set myBar = CommandBars.Add _
   (Name:="MyShortcut", Position:=msoBarPopup, Temporary:=True)

'And now we add menu items to the new
'command bar's controls collection.
Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   'The menu item's text. Putting "&" in front of a letter
   '(here "M") means, that you can press M instead of clicking.
   .Caption = "&Menu item 1..."

   'The procedure/macro to run if this menu item
   'is selected. In this case the macro "Dummy1", which
   'will just pop up with a message.
   .OnAction = "Dummy1"

   'There are thousands of FaceId icons, if you
   'want a small icon before the text.
   .FaceId = 133

   'Below (as a comment) an example on how the value of
   'a variable (here the non-declared "MyVar") can
   'determine, if a menu item should be visible or not.
   'You can also let it be visible, but disable it by
   'setting ".Enabled = False".
   'The point is simply that you can change the look and
   'functionality dynamically depending on other parameters.
   'If MyVar = 0 Then
      '.Visible = True
   'Else
      '.Visible = False
   'End If
End With

'Add menu item 2 using the same "recipe".

Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   .Caption = "M&enu item 2..."
   .OnAction = "Dummy2"
   .FaceId = 133
End With

'Menu item 3
Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   .Caption = "Me&nu item 3..."
   .OnAction = "Dummy3"
   .FaceId = 1848
End With

Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   .Caption = "Men&u item 4..."
   .OnAction = "Dummy4"
   .FaceId = 387

   'Indicate a new "group" by inserting a line.
   .BeginGroup = True
End With

Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   .Caption = "Menu &item 5..."
   .OnAction = "Dummy5"
   .FaceId = 109
End With

Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   .Caption = "Menu i&tem 6..."
   .OnAction = "Dummy6"
   .FaceId = 19
End With

Set myItem = myBar.Controls.Add(Type:=msoControlButton)
With myItem
   .Caption = "Menu it&em 7..."
   .OnAction = "Dummy7"
   .FaceId = 4
End With

Exit Sub
ErrorHandle:
MsgBox Err.Description & vbNewLine & _
"Procedure CreateShortcut.", vbCritical, "Error"
End Sub

That was the first step. You can make as many menu items as you wish. Below are the the 7 demo macros called by the menu.

Sub Dummy1()
MsgBox "Menu item 1"
End Sub
Sub Dummy2()
MsgBox "Menu item 2"
End Sub
Sub Dummy3()
MsgBox "Menu item 3"
End Sub
Sub Dummy4()
MsgBox "Menu item 4"
End Sub
Sub Dummy5()
MsgBox "Menu item 5"
End Sub
Sub Dummy6()
MsgBox "Menu item 6"
End Sub
Sub Dummy7()
MsgBox "Menu item 7"
End Sub

Private Sub Worksheet_BeforeRightClick _
(ByVal Target As Excel.Range, Cancel As Boolean)

Dim rIsect As Range

On Error GoTo ErrorHandle

'We check if the mouse right-click was in the
'named range "Area". If you don't have a range with
'the name "Area", the following line will trigger
'an error.
Set rIsect = Application.Intersect(Range("Area"), Target)

'If TRUE, the menu will pop up, if FALSE Excel's standard
'menu is displayed. If you don't care where the user
'clicks, you can omit all this and only use the 2 lines:
'CommandBars("MyShortcut").ShowPopup
'Cancel = True

If Not rIsect Is Nothing Then
   CommandBars("MyShortcut").ShowPopup
   Cancel = True
End If

BeforeExit:
Set rIsect = Nothing

Exit Sub
ErrorHandle:
MsgBox Err.Description
Resume BeforeExit
End Sub

At this page you can find all the small FaceId icons (with corresponding numbers) that you can use in your menu.