Back
Excel

Creating a Custom Popup Menu

Today’s author is Derek Mang, owner of Systems Solution Developers Inc. You can find more useful tips from Derek on his website: www.officevbasolutions.com.

This post shows you how to create a custom popup menu in Excel.

Microsoft Excel has lots of popup menus that are made available to the user with a right-click depending on what your doing – hence the term context menu. Right-click on a cell and you get the Excel “cell” popup menu and its available choices. This menu is subject to customization, allowing you to add items to the menu or disable menu items if desired.

Depending on your needs, a dropdown menu could get to be awkwardly large. Furthermore, you get the same menu for each cell, and if there are dependencies on cell content you may wind up with too many choices. A completely custom menu based on cell content or range, would better satisfy specific needs.

I found an example of the custom menu on the net and extended this using a worksheet class module to include all worksheets in a workbook rather than just one as found in the original example.

The following code will create and popup with three custom menus (Red, Yellow and Green) on any worksheet in the current workbook where the right-clicked cell is a shade of red, yellow or green respectively.

The workbook requires code in the Workbook.Open event, as well as one code module, and one class module.

The key is in the class module. The class module contains handlers for worksheet events that are fired whenever an action occurs on (in this case) any worksheet in the workbook. Of particular interest is the Worksheet.BeforeRightClick event. As the event name suggests, “what do you want to do before the default action that occurs when a user right-clicks in a worksheet.†This opens up all kinds of possibilities all based on the cell that has been right-clicked by the user.

In this sample, the Range.Interior property is used to access the cell’s (aka target) Interior.ColorIndex property, and then depending on the value for color returned, the default popup menu is cancelled and corresponding custom popup menu is shown depending on the property value returned.

Since discovering this technique, I have included it – and it has been well-received – in several custom Excel solutions to limit end-user activity to specific tasks.

Workbook_Open

The code for the Workbook.Open event handler establishes the three popup menus, and sets up the worksheet objects in the class. Open the Visual Basic Editor and paste this code into the ThisWorkbook module of an Excel Macro-Enabled Workbook (.xlsm) to define the event handler for the Workbook.Open event.

Code Module

The code module contains the class set up and the actual menu creation routines. In the Visual Basic Editor, click Module on the Insert menu, and then paste this code into the module.

Class Module

The class module determines which menu to “popup” depending on characteristics of the target cell. In the Visual Basic Editor, click Class Module on the Insert menu, change the name of the class module to clsWS, and then paste this code into the module. (Be sure to change the name to clsWS so that it will be available as the class of that name for the SetupAllWSEvents() subroutine.)

To demo the code, set the fill color of several cells to shades of red, yellow, and green, close the workbook, and then re-open it to run the Workbook_Open() code that sets up the popup menus. When you right-click the colored cells, the popup menus should look like the following examples.

clip_image002 clip_image002[4] clip_image002[6]

Acknowledgements

The original sample that provided inspiration and insight was authored by Andy Wiggins (Byg Software)