Up next in 10
Creating Your Own Right-Click Menu for Specific Cell Ranges in Excel
2K views
Jul 6, 2024
In this video, we'll demonstrate how to customize Excel's context menu to display specific options when right-clicking within a designated cell range, and how to ensure the default context menu appears outside this range. We'll also cover how to prevent the custom menu from appearing in other sheets or workbooks. #excel #vba #exceltutorial #exceltips
View Video Transcript
0:00
Welcome to this tutorial today. I'll show you how to replace the default context menu with your own custom items
0:06
but only for a specific range of cells. First open the VBA editor by pressing alt F 11
0:12
insert a new module by right clicking on any existing module or the VBA project selecting and then module
0:20
Let's start by writing a subroutine my menu to customize the context menu
0:24
First declare a variable cell menu to reference the context menu, then delete all existing context menu items
0:32
looping through them. Next we'll add our custom menu items with icons
0:37
We add two items save in option two, test the code, click inside the my menu subroutine and press F five to execute it
0:45
Right. Click on a cell. You'll see the context menu. Now shows only the two items we added
0:50
Go back to the VB editor, write a subroutine called default menu to reset the cell's context menu to its default state
0:57
Click inside the default menu subroutine and press F five to execute it right
1:02
Click on a cell again, you'll see that Excel is showing the default context menu
1:06
Now return to the VBA editor and write two more subroutines. These subroutines will run in response to user actions
1:13
For example, when a user chooses option two from the custom menu action for second button subroutine will execute
1:20
You can customize these subroutines to perform any actions you need. For example
1:25
when you click save from custom menu subroutine, saves the workbook and displays a message box
1:30
We've created two subroutines. One to delete all context menu items and add two custom items and another to reset the context menu to its
1:38
default state by clearing customizations. Next, we want the custom context menu to appear only when right clicking within a specific range
1:46
If we right click outside this range, default Excel context menu should be displayed to do this
1:51
Go to the VBA project explorer and double click the sheet object containing the cell range where you want the custom menu to appear
1:57
In our case, we'll display the custom menu for the range A one B 10 on sheet
2:02
one in the sheet, one code editor, insert worksheet before right click event procedure
2:07
This procedure runs automatically each time you right click in the sheet if statement checks if the target cell where you right
2:14
clicked within the specified range. If it is my menu subroutine activates the custom menu
2:20
If the right click is outside the range. The default menu subroutine resets the context menu to its default state
2:26
right? Click on a cell in range A one B 10 to display the custom menu now switch to sheet two
2:32
right click any cell, you'll notice that the custom menu unexpectedly appears
2:37
This happens because sheet one contains the code that checks where the right click occurred
2:41
If the click is outside the specified range, resets the menu. When you right click a cell within the custom menu range
2:48
VBA deletes the default context menu. However, when you switch sheets, the same custom menu appears to prevent the custom menu from appearing in other
2:56
worksheets, insert the worksheet, deactivate procedure in the sheet one object and call the default menu subroutine
3:03
This action will reset the context menu whenever sheet one is deactivated next
3:11
right, click on C A one and then open a new workbook, you'll see the custom menu also appears in the new workbook to prevent
3:18
the custom menu from showing in other workbooks. Close the newly opened workbook
3:22
go back to the VBA editor and double click the this workbook object to open its code editor
3:27
insert the workbook deactivate procedure and call the default menu subroutine. This subroutine runs automatically when the current workbook becomes inactive
3:36
causing the default menu subroutine to reset the text menu. Close the VBA editor and right click on cell
3:42
a one to activate the custom context menu. Now open a new workbook and right click any cell
3:48
You'll see that the custom context menu does not appear in the new workbook. The workbook deactivate procedure resets the context menu whenever the workbook
3:56
is deactivated. Thank you for watching. Don't forget to like share and subscribe if you have any questions or need further clarification on anything in
4:04
this video. Feel free to leave a comment below