A context menu is represented by a single class of objects — CommandBar. These objects, as you already know, have been preserved from earlier versions of Excel. They make it possible to contain both buttons with icons and drop-down lists at the same time.
To build your own context menu in a workbook, you should follow these steps:
- Write procedures that add your custom context menu when the workbook opens and remove it when the workbook closes.
- Create a new context menu.
- Add controls to the context menu and link them to macros or VBA procedures.
- Define the moment (or place) where the context menu should be displayed.
- Ensure that after your custom context menu is displayed, the built-in Excel context menu does not appear.
The created context menu is displayed on screen using the ShowPopup method of the CommandBar object.
To define the moment when the context menu should be displayed, the best choice is the event procedure BeforeRightClick of the Worksheet object. The parameter Target of this procedure allows you to specify the worksheet range where this menu should be displayed. If you want the context menu to appear when right-clicking anywhere on the worksheet, you do not need to specify a value for the Target parameter.
By setting the parameter Cancel of the SheetBeforeRightClick event procedure of the Workbook object to True, you can disable the execution of default functions associated with right-clicking.
The demonstrates the creation of a context menu with the following elements:
- Number Format command (opens the Format Cells window on the Number tab),
- Font command (opens the Format Cells window on the Font tab),
- a separator (to start a new group),
- Open, Save As, and Exit commands (these elements perform the same functions as the corresponding buttons on the File tab).
The custom context menu is displayed when right-clicking in the range A1:L25 on Sheet1 (this cell range is given the name menu).
All the necessary code listings can be found in the corresponding modules: ThisWorkbook, Sheet1, and Module1 of the given example file.