How to add custom button to the right click/context menu in Excel?



Excel gives you access to a Context menu that is loaded with options whenever you right-click on a cell. This menu gives you the ability to perform a wide variety of tasks. It's possible that you should add some features to the Context menu, especially if those features are ones that you use frequently. Unfortunately, you cannot change Context menus in the same way that you can edit other menus, which is by selecting Customize from the Tools menu. This is the only way to edit Context menus. The Cell menu is the most frequent form of the Context Menu that most people are familiar with and make use of.

The purpose of this article is to discuss the process of adding a button for custom code to the right-click menu in Excel using VBA code.

Add Custom Button to The Context Menu with VBA Code

Step 1

Open an excel sheet and Press Alt and F11 key (Alt+F11) to open Microsoft Visual Basic for Application windows. See the below given image.

Step 2

After that, select Insert > Module from the menu bar to bring up the popup Module window. See the below given image.

Step 3

After opening the Module Window, then type the following VBA code in it.

Sub AddItemToContextMenu()
   Dim cmdNew As CommandBarButton
   Set cmdNew = CommandBars("cell").Controls.Add
   
   With cmdNew
      .Caption = "My Button"
      .OnAction = "MyButton"
      .BeginGroup = True
   End With
End Sub

After adding the VBA code, Press F5 or click on Run tab. Then Press the Alt + Q keys to exit the Microsoft Visual Basic for Applications window.

The only thing you need to do is update is .Caption property to any menu text you want to use, and Adjust the .OnAction property so that it points to the desired command or macro to be executed.

You can use the following macro if at a later time you decide that you no longer desire the menu option.

Sub RemoveContextMenuItem()
   On Error Resume Next
   CommandBars("cell").Controls("My Button").Delete
End Sub

To make advantage of this, replace the text that says "My Button" with the text that you used in .Caption property of the macro written above. In this particular macro, the On Error statement is present simply in case the designated macro item had not been included in the earlier additions.

Now select any cell and right click there You will see “My Button” button added to the list.


Advertisements