When we create a Macros, we need a procedure to call or run the macros. There are so many methods to call modules/vba code. Adding a Custom menu to Context Menu (Right Click Menu) to call VBA Macros is a topic of this article.
So many times you have observed that when we click right mouse button a list of text items appears, it is called as context menu and when we click these items, a specific task performs. How it works and how we can add our own menu items to this context menu? Can we customize this context menu? The answer is yes. Look at the image (fig-1) below. Two pink circles are shown in this image, it means we have added our own menu items to this context menu.
To add our own menu item to the context menu press Alt+F8 and create three macros as shown in Image below(Fig-2)
In the next step, click on Module1 and write the following vba code (four macros) as shown in Fig-3 below
Now double click on ThisWorkBook and write the following code. The purpose of writing this code in Workbook is, this code will execute before showing of excel Worksheet. You can write or choose your own menu name according to nature of your task.
You have to take care of one thing that is method name ( i.e.’ CalculateAverage’, ‘Reset’, ‘About’ ) written in module1 should be matched with the code written in ThisWorkBook at arrow no 3 shown in following image (fig-4) otherwise it will generate error.
Arrow No 1: Menu name, it will show in the Context menu. You can write any Name according to your task.
Arrow No 2: Sub Menu Name, it will show when you place your mouse cursor on your custom Menu.
Arrow No 3: It is a Method Name where you write vba code. When user click on Sub Menu it will search the method and execute the code.
Arrow No 4: It will decide the order or position of Sub Menu.
The purpose of this article is only to show how we can add our own menu to Context menu or Right Click Menu. The code written in the methods (Fig-3) are only for example purpose, it will work when you feed the data in excel sheet exactly shown in the Fig-1 otherwise it will produce error.