Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
3K+ Views
