Microsoft Office 2007 Enterprise Creating a Custom

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.

Private Sub Workbook_Open() Set cb_Red = CreateSubMenu("Red") Set cb_Yellow = CreateSubMenu("Yellow") Set cb_Green = CreateSubMenu("Green") Call SetupAllWSEventsEnd Sub 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.

Option ExplicitGlobal cb_Red As CommandBarGlobal cb_Yellow As CommandBarGlobal cb_Green As CommandBarGlobal WSObj As CollectionGlobal ws As Worksheet
Sub SetupAllWSEvents() Dim WSo As clsWS Set WSObj = Nothing Set WSObj = New Collection For Each ws In ActiveWorkbook.Worksheets Set WSo = New clsWS Set WSo.WSToMonitor = ws WSObj.Add WSo, ws.Name Next ws End Sub Function CreateSubMenu(strCB) As CommandBar Const CBPREFIX = "CustomPopUp" Dim cb As CommandBar Dim cbc As CommandBarControl Dim strCBName As String 'custom menu name strCBName = CBPREFIX & strCB 'remove previous instance Call DeleteCommandBar(strCBName) 'Add our popup menu to the CommandBars collection Set cb = CommandBars.Add(Name:=strCBName, _ Position:=msoBarPopup, _ MenuBar:=False, _ Temporary:=False) 'Add controls Set cbc = cb.Controls.Add With cbc .Caption = strCB & " &Control 1" .OnAction = "DummyMessage" End With Set cbc = cb.Controls.Add With cbc .Caption = strCB & " Control &2" .OnAction = "DummyMessage" End With Set CreateSubMenu = cb Set cbc = Nothing Set cb = Nothing End FunctionSub DeleteCommandBar(cbName) On Error Resume Next CommandBars(cbName).Delete End SubSub DummyMessage() MsgBox CommandBars.ActionControl.Caption,Windows 7 64 Bit, vbInformation + vbOKOnly, "Dummy Message"End Sub 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,Office Standard 2010, 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.)

Dim WithEvents aWS As Worksheet Property Set WSToMonitor(uWS As Worksheet) Set aWS = uWSEnd Property Private Sub aWS_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) Select Case Target.Interior.ColorIndex Case 3,Office 2010 Pro Plus Key, 9 cb_Red.ShowPopup Cancel = True 'defeat the standard cell popup menu Case 4, 10, 35, 43, 50, 51, 52 cb_Green.ShowPopup Cancel = True Case 6, 12,Office Pro 2010 Key, 36, 44 cb_Yellow.ShowPopup Cancel = True Case Else Cancel = False End SelectEnd Sub

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,Microsoft Office 2007 Enterprise, the popup menus should look like the following examples.

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