Up next in 10
Adding a Custom Submenu to Excel's Right-Click Menu Using VBA
1K views
Jul 6, 2024
00:00 Write VBA code to create a submenu 02:07 Automatically initialize the submenu In this video, you'll learn how to create a custom submenu in Excel's right-click context menu using VBA. We'll walk through adding multiple items to the submenu, ensuring it only appears in the intended workbook. By the end of this tutorial, you'll be able to enhance your Excel context menu with personalized options for improved functionality. #Excel #VBA
View Video Transcript
0:00
My previous video showed you how to add a single item to the context menu
0:04
In this video, explain how to create a sub menu within the context menu
0:08
When you right click on any cell in a worksheet. Your own sub menu with multiple items will be visible
0:13
Open the VBA editor by pressing alt F 11 and insert a new module
0:18
Double click the module to open its code editor. Let's start by creating a subroutine called a sub context menu
0:24
We'll call this sub routine to add a custom sub menu to the right click context menu
0:29
Declare four variables as shown on the screen. Cell menu references the cell context menu sub menu represents our new sub menu item one and
0:38
item two represent the items within the sub menu. Next code chunk removes existing sub menu
0:43
If any the next line sets the cell menu to the cell context menu set sub menu adds a new pop up control to the
0:50
cell context menu set. Item one code adds a button control to the sub menu item one dot caption sets the caption of the button
0:57
item one dot on action assigns a subroutine to run when the button is clicked
1:02
Next code chunk creates the second button for the sub menu. You can add as many buttons as you need
1:08
Giving each a caption and assigning a subroutine. This way each button can perform its specific function according to your requirements
1:15
Next, create two subroutines, subroutines execute when we click on the sub menu items
1:21
the save macro subroutine saves the active workbook and displays a message box
1:25
While the do something macro subroutine simply displays a message box. Now click inside the add sub context menu subroutine and press F five to
1:34
execute it. Running this subroutine will add the sub menu to the context menu
1:38
right? Click on any cell to see our custom sub menu in the context menu
1:42
Clicking the save item will save the workbook and clicking the option two item will display a message box
1:48
As mentioned earlier. You can add as many buttons as you need in the sub menu
1:51
Let's add a third item this quickly I copied and pasted the code of an existing item and modified it to create the third sub
1:58
menu item again, press F five. While the cursor is inside the a sub context menu subroutine
2:05
you can see the third item is added in the sub menu. Next, we create another sub routine that deletes the sub menu from the
2:11
context menu. We won't call this sub routine directly. Instead we use the workbook
2:16
deactivate event to run it to set this up double click. The this workbook object to open its editor first
2:22
Insert the workbook, activate event procedure and call the a sub context menu subroutine
2:28
This will add the sub menu to the context menu each time the workbook is activated
2:32
Next, insert the workbook deactivate event procedure and call the remove context menu subroutine
2:38
This will delete the sub menu from the context menu by using these events
2:42
You ensure that your custom sub menu is not visible in any other workbooks context menu
2:47
The sub menu gets deleted when the workbook containing the code is deactivated and is re added when the workbook is activated again
2:54
This technique ensures that the sub menu does not appear in any other workbook test this a new workbook and right click on a cell
3:01
in the newly opened workbook. You'll notice that it does not include the custom sub menu
3:06
Switch back to the workbook containing the code. When you right click on a cell
3:10
you'll see the custom sub menu along with the default tools. Thanks for watching
3:14
Please like share and subscribe. If you have any questions about this video, feel free to ask