Add Button To Cell Right-Click Menu Excel VBA Macro

The cell right-click menu in Excel, also known as context menu, is a fast way to see and change properties of a cell or range in the worksheet. We can use VBA macros to add controls to that menu such as buttons that call a certain macro or run a function. This macro example targets the CommandBar object in order to add a button to the cell menu that runs a particular macro. We can add more buttons to the menu, or other controls such as sub-menus (popup control). Buttons can have a custom icon using the property FaceId. Find the list of icons available in Microsoft Office in this other page: Command Bar Button FaceId

 

Macro code:

  

  Sub AddButtonToCellMenu()
 
      Dim cmdBar As CommandBar
      Dim cmdButton As CommandBarButton
 
      Set cmdBar = Application.CommandBars("Cell") 'or index 35
 
      Set cmdButton = cmdBar.Controls.Add(Type:=msoControlButton, Before:=1)
 
      With cmdButton
          .OnAction = "MyMacro1"
          .Caption = "Run my macro"
          .FaceId = 59
      End With
 
  End Sub

Macro explained:

• First, we declare two object variables (cmdBar and cmdButton) to store the command bar and command button objects.
• We target the cell command bar or right-click menu via the CommandBar object, and assign it to the object variable cmdBar. CommandBars returns an object that represents an Excel command bar. In this case, we specifically target the cell command bar or context menu (index 35). We could target other command bars with the relevant index or name. For example, index 1 corresponds to the worksheet menu bar or ribbon.
• Next, we add a button to the cell command bar or right-click menu (defined as cmdBar) by specifying that type of control (msoControlButton). Note that other types of command bar controls (as per msoControlType enumeration) may not be applicable in the case of the cell menu (they may be applicable with other types of menus). Another parameter (Before) specifies the position inside the right-click menu. In the example above, the button is added at the very top of the menu (before item 1).
• Finally, we need to add a caption to the button and assign the macro or function to run when clicked (OnAction). Additionally, we may want to add an icon associated with the menu button. We can do that with the property FaceId. Find the list of Microsoft Office 2016 FaceId icons for a command bar button in this other page: Command Bar Button FaceId

If needed, we can delete the button addressing the caption of the control as indicated below.

  CommandBars("Cell").Controls("MyMacro1").Delete

 

We can also add a sub-menu within the cell context menu, and then add buttons there. To do that, we add first a popup control (msoControlPopup) to the cell menu instead of a button.

 
  Dim cmdPopup As CommandBarPopup
  Set cmdPopup = cmBar.Controls.Add(Type:=msoControlPopup, Before:=1)
  cmdPopup.Caption = "My sub-menu"
 

Then we add the buttons inside that sub-menu as we did before, but referencing the popup control defined as cmdPopup.

 
  Set cmButton = cmPopup.Controls.Add(Type:=msoControlButton)
 

 

There is yet another way to do all this, which consists of adding items to the cell menu using Application.ShortcutMenus. The structure is slightly different, but the outcome is similar. This method is rather used in Microsoft Access though, and seems to add the menu only temporarily when used in Excel. Nevertheless, we could use a workbook open event procedure to call the macro and add the menu using this method when the workbook opens.

This is how we add a button to the cell right-click menu in Excel using VBA macros.

Other examples:

• How to Import Pictures From Google Drive UPDATE 2023 with Excel VBA macros
• How to Import Specific Cells From Google Sheets with Excel VBA macros
• How to Unhide All Sheets And Cells with Excel VBA macros