Please could you assist. With Excel 5 I could add a short-cut menu with :

Sub Add_ShortCut_Menu(Macro_tobe_Run$, Menu_Caption$,

Menu_Item_Before$)


' Written by Robert L. Nelson\\1998 January 2,4,5


' This sub adds a menu item to the right-mouse 


' button on a WS


' Dim Macro_tobe_Run$, Menu_Caption$, Menu_Item_After$


Dim MenuItemAdded


    Set MenuItemAdded =


Application.ShortcutMenus(xlWorksheetCell).MenuItems_


        .Add(Caption:=Menu_Caption$, _


            OnAction:=Macro_tobe_Run$, _


            Before:=Menu_Item_Before$)


End Sub

However, I am unable to find anywhere where it says how to do this under Excel97? Any help would be most welcome.

Robert Nelson.

PS : Have just been reading your book Excel 97 Annoyances.

Robert,

Compatibility is an amazing thing. It allows the implementation of a particular feature -- like menus, in this case -- to be changed completely without breaking existing code. At least, that's the promise of compatibility -- a promise that, more often than not, falls short.

You may be wondering why I'm babbling on about compatibility. The reason, of course, is that, through Office 95, the menus in Office's various components were created by calling the standard Windows API functions, and were made programmatically accessible by using some language feature that was unique to each Office component. With Office 97, Microsoft did away with this in one fell swoop:

• Office menus ceased to be standard Win32 menu structures, but instead are created using a object model that was common to all Office 97 applications.

• A common language (in the now VBA-enabled Office suite) is used to work with menus programmatically.

In this case, however, Microsoft appears to have delivered on its promise. The Excel 5 code (Click here for code example) adds an item to the worksheet's shortcut menu If you use Excel 97 to open a workbook created using Excel 5, the code executes successfully. If you enter the code from scratch into an Excel 97 workbook, it will create the menu item and invoke the procedure designated by Macro_tobe_Run$ provided that Macro_tobe_Run$ is declared to be a public procedure.

This Excel 5 code is fairly straightforward. The Excel Application object maintains a ShortCutMenus collection; one of its members, designated by the xlWorksheetCell constant, represents the worksheet's context menu. Its MenuItems collection define its menu items.

But compatibility is a dangerous thing, since there's no guarantee that it will always be supported. In addition, while the basic design of the Excel 5 menu system is straightforward enough, it harmonizes poorly with both the extensible character of the Office 95 menu system and with the more or less object oriented character of VBA and the Office object models. (You can't, for example, iterate the ShortcutMenus collection using a For Each...Next loop.) Clearly, it's preferable to use the Office 97 way of building a menu. And this has very little in common with the Excel 5 menu system.

To simplify somewhat, in Office 97, all shortcut menus are members of the CommandBars collection. The shortcut menu that appears when we click the right mouse button on a worksheet cell is named Cell. Each item on a shortcut menu is represented by a CommandBarControl object; these can be accessed from the CommandBar object's Control's collection. So, for example, if we wanted to add a custom item, My Own Macro, immediately before the Insert Comment menu option, we could do it as follows: (Click here for code example)

This code is fairly straightforward. We first retrieve a reference to the Cell shortcut menu from the CommandBars collection. If the reference is valid, we then determine the position of the Insert Comment item on the shortcut menu. Finally, we call the Add method of the shortcut menu's Controls collection; this adds a menu item (represented by a CommandBarControl object) to the Controls collection. The syntax of the Add method is: (Click here for code example)

If you're interested in working with Excel and customizing its menu system, be sure to see the detailed treatment in Writing Excel Macros, by Steven Roman, which will be published in May 1999.

-- Ron

Return to: Ron's VB Forum