Replacing API Calls That Manipulate Menus

Paul Litwin, Ken Getz, and Mike Gilbert
Sybex, Inc.

Created: Wednesday, August 28, 1996
Revised: Thursday, August 29, 1996

The information in the document that follows will appear, in some altered format, in “Access 97 Developer’s Handbook”, by Paul Litwin, Ken Getz, and Mike Gilbert (© 1996, Sybex, Inc.)

Introduction

Unlike other Office applications, Microsoft Access has never included extensive support for controlling and modifying its menus. Setting the checked or grayed state for a menu item was difficult, until the addition of the SetMenuItem method in Access 95. Retrieving those states was still impossible without going outside the confines of the product.

To work around these limitations, many Access developers turned to the Windows API. Calls to EnableMenuItem, CheckMenuItem, and TrackPopupMenu showed up in many applications. In the transition from Access 2 to Access 95, many developers converted their 16-bit API calls to the 32-bit environment.

Both Word and Excel have made working with menus far easier than Access ever did, so few Word or Excel developers will most likely need to handle the problems facing many Access developers. Therefore, this document is focused mainly for those developers converting applications to Access 97.

In order to provide a unified object model for toolbars and menu bars, the Office team has provided the new CommandBar object hierarchy. A CommandBar object can appear in many guises, most obviously as a standard menu, or as a toolbar. Although the menu bar incarnation of a CommandBar resembles a standard Windows menu, it actually is a very different beast. Therefore, the bad news is that any code you have that uses the Windows API to reference menus will fail in Office 97. Even code that calls into menus created with macros will no longer work – those menus are created through the CommandBar code, as well.

This document will provide some pointers and examples of using CommandBars and their object model to replace your existing code. Rather than focusing on individual API calls, however, we’ll provide sections on handling the most common scenarios that caused developers to work outside the tools Access provided natively, and suggest methods for achieving the same results, using the CommandBar object model instead of API calls.

Note   The SetMenuItem method of the DoCmd object will continue to work, of course. You won’t need to change code that uses this method, although it will work only with menus you’ve created with macros.  It never worked with built-in menus, and it won’t work on menus you create as CommandBars.

Getting Started

The Object Browser and the Office on line help file are the first places to start as you attempt to work with CommandBars. The object model is somewhat dense, however, so here are some important ideas to get you started.

• The CommandBars collection is at the top of the CommandBar object hierarchy. Every menu bar and toolbar is an element of this collection.
  
  
• Although you can refer to an element of the CommandBars collection by its ordinal position in the collection, you won’t want to, for the most part. When you start Access (or any Office application), the CommandBars collection contains all the built-in items, and the application hides and shows them as necessary. Therefore, you can’t figure out an item’s index by looking at a menu or toolbar: all the hidden items take up an index “slot” as well. Instead, you’ll most often refer to elements in CommandBars’ collections by name.
  
  
• The main menu bar’s name is “Menu Bar”. To work with any of the built-in menus, you’ll always start your references with “CommandBars(“Menu Bar”). To create a reference to the form/report design Toolbox, on the other hand, you might write code like this:

Dim cbr As CommandBar
Set cbr = CommandBars("ToolBox")

• You can use item names without the “&” (indicating the position of the hot key), but the names are case-sensitive. That is, using “Filter By Form” will not work if the command name is “Filter by Form”. In addition, the trailing “…” included on some menu items is significant – if it’s there on the menu, you must include it in your object reference.
  
  
• The objects on a given CommandBar are all controls. Whether you’re displaying the CommandBar as a toolbar or a menu bar, most elements are command buttons. The difference between a toolbar and a menu is how the button is displayed! Toolbars can also display controls besides command buttons (drop-down lists, for example).
  
  
• Divider lines in menus, and spaces in toolbars, aren’t counted as controls. To indicate to the CommandBar that you want a divider line before a control, set its BeginGroup property to True. The divider/space has no effect on any item counts, nor does it affect indexes within collections.
  
  
• The controls used on CommandBars are specific to their implementation, and aren’t ActiveX controls, intrinsic Access controls, Office forms controls, or VBX controls. You cannot create your own control types, nor can you add to the list of controls that you can place on Access’ CommandBars: You can add buttons, edit controls, dropdown lists, comboboxes, and popup menus.  That’s it.
  
  
• Given that all the objects on a CommandBar are controls, refer to the objects on a CommandBar using its Controls collection. For any control that is a popup control (as are all items on the main Access menu), use the CommandBar property of the control to drill down into the CommandBar that the control pops up. Therefore, to retrieve a reference to the “Show Debug Window” item on the View menu in Access, you might write code like this:

Sub ShowDebugWindow()
  With CommandBars("Menu Bar").Controls("View"). _
   CommandBar.Controls("Debug Window")
    ' If the menu item is visible, execute its action
    ' (that is, display the Debug Window).
    If .Visible Then
      .Execute
    End If
  End With
End Sub

• Use the OnAction property of CommandBarControl objects to execute an action when you select the item.  The property can contain a string expression resolving to either the name of a macro or a function call, in the form “=FunctionName()”. In order to cause a menu item to be checked, or to create a two-state button, the control must have its OnAction property set to call something. (To see two-state buttons in action, check out the View menu when the Database Explorer is selected: the Large Icons, Small Icons, List, and Details items all use a two-state button to indicate the current setting.)
  
  
• From within the code called by the string in the OnAction property, you can use the ActionControl property of the CommandBars collection to retrieve a reference to the control that called the code. This makes it possible to take different actions, depending on the menu or toolbar item that was just selected. If you’ve placed information in the control’s Tag property, you can also use that in your code, once you’ve used the ActionControl property to find out just which control called the code.
  
  
• A built-in CommandBar item’s ID property indicates the action that control will take when it’s selected.  Setting a user-created control’s Id property to match one of the built-in Id values will make the item look like a built-in control, but not act like it. That is, assigning your new CommandBarControl item the Id property of the Edit|Cut control will set the text and the picture to match the built-in control. This will not, however, cause your new item to perform the action associated with the Edit|Cut menu item. If you want to take an action, you’ll need to set the OnAction property to call a macro or a function that performs the action.

Doing Things the CommandBar Way

Once you’ve got the “hang” of working with CommandBars, you’ll want to start replacing your existing calls to the Windows API with code that’ll operate correctly with the new object model. The following sections will provide details on making those replacements. Although there’s no way to provide a one-to-one correspondence between specific API calls and VBA code, the sections will attempt to help in the conversion.

Retrieving a Menu Handle

When working with the Windows API, almost every menu manipulation required you to provide a valid menu handle as one of the parameters to a function call. These menu handles that are nowhere to be found in the CommandBar world, and so you’ll never make a call to the GetMenu or GetSubMenu API functions again.

Instead of retrieving menu handles, you’ll need to retrieve and use references to CommandBar objects. To use an existing CommandBar object (for example, the menu hanging off the View|Database Objects menu item), use code like this:

Dim cbr As CommandBar
Set cbr = CommandBars("Menu Bar").Controls("View"). _
 CommandBar.Controls("Database Objects").CommandBar

This technique handles references to either the top-level menu (just refer to CommandBars(“Menu Bar”) or  to any of the popup menus hanging off the top-level menu (as in the previous example).

Also, note that you’ll most often refer to menu items by name or, if you need to iterate through the collection of menus, using a For Each…Next loop. Although calls to the GetSubMenu API function required you to pass an ordinal value representing the position of the item you needed, you won’t use this technique with CommandBars.

Retrieving a Reference to a Specific Menu Item

Calls to the Windows API that manipulate specific menu items generally need a menu handle, and an offset into the menu in order to do their work. With CommandBars, you’ll retrieve a reference to a particular CmmandBarControl object, and work with its properties and methods.

To retrieve a reference to a specific menu item, use the CommandBar property of a CommandBarPopup control (that is, a menu item that causes another menu to pop up, as opposed to taking some action). For example, to work with the Delete item on the Edit menu, you could write code like this:

Dim cbc As CommandBarControl
Set cbc = CommandBars("Menu Bar"). _
 Controls("Edit").CommandBar.Controls("Delete")

Retrieving the Number of Items in a Menu

If your code is calling the GetMenuItemCount API function, you might try replacing it with code that retrieves the Count property of a given CommandBar. This won’t work, however, because the Count property also includes all the items that aren’t visible in the current context. You can use something like the following function to replace calls to the GetMenuItemCount API function:

Function GetMenuItemCount(cbr As CommandBar, _
 Optional ByVal CountAll As Variant) As Integer
 
  ' Given a CommandBar reference, return the
  ' number of items it includes. Optionally, count
  ' all the items. The default is to just count visible
  ' items.
    
  ' In:
  '    cbr: a reference to an existing CommandBar
  '    CountAll: (optional) True/False, indicating whether
  '      to include all menu items, or (default) only
  '      visible items. 
  ' Out:
  '    Return value: the count of items, either visible, or all,
  '     depending on the value of the CountAll parameter.    

  Dim cbc As CommandBarControl
  Dim intCount As Integer
  Dim fCountAll As Boolean
    
  ' Assume that you don't want to count all
  ' the items in the menu.
  If IsMissing(CountAll) Then
    fCountAll = False
  Else
    fCountAll = CBool(CountAll)
  End If
  For Each cbc In cbr.Controls
    ' Increment the count if either
    ' you're counting all, or this item
    ' actually is visible.
    If fCountAll Or cbc.Visible Then
      intCount = intCount + 1
    End If
  Next cbc
  GetMenuItemCount = intCount
End Function

Disabling/Enabling a Menu Item

Disabling and enabling (often called “graying”) a menu item is as simple as changing the Enabled property of a control. Once you’ve got a reference to the item you want to enable or disable, set the value of its Enabled property, and you’re all set. Using the Windows API, this required you to retrieve a menu handle, and then call the EnableMenuItem API function.

To disable the Edit|Undo Typing menu item using CommandBars, you might write code like this:

Dim cbc As CommandBarControl
Set cbc = CommandBars("Menu Bar"). _
 Controls("Edit").CommandBar.Controls("Undo Typing")
cbc.Enabled = False

Note   Attempting to enable a menu item that Access wants disabled will be a fruitless exercise. You’re perfectly welcome to disable an enabled menu item, and Access will respect your changes. On the other hand, if you attempt to enable a menu item that Access thinks should be disabled, your change will be discarded without triggering an error. For example, attempting to enable the Edit|Can’t Undo menu item will have no effect, as long as Access thinks it ought to be disabled. You will be able to programmatically disable the Edit|Undo Typing item, however, even if Access would present the item as enabled.

Checking/Unchecking a Menu Item

From a CommandBarControl’s point of view, the “checked” state of a menu item is the same as the “selected” state for a two-state toolbar button. For example, in Form Design view, the Align Left, Center, and Align Right toolbar buttons could also be represented on a menu, with one of the three items being “checked”. Using the Windows API, this required retrieving a menu handle for the parent menu, and then using the CheckMenuItem API function.

You won’t find a “Checked” property in the Object Browser or Online Help, because it doesn’t exist. Instead, you’ll use the State property of a CommandBarControl object to control the checked condition. In order to work with the checked state, your control must follow the following rules.

Your selected control must:

• Either have no specific value set for its Id property, or have a value set that doesn’t insert a picture to the left of the menu item. (You can also set the Style property so that the control doesn’t display the associated image.)
  
  
• Not be a built-in control. (Of course, you can convert a built-in control to be not built in by setting its OnAction property!)
  
  
• Call a macro or function from its OnAction property.

So, when can you control the State property of a CommandBarControl? If you’ve created the item, set it up to call a macro or function when selected, and haven’t set an image or explicitly hidden the image, then you will be able to set its State property.

For example, the following code example creates a new menu containing five colors. Each item on the menu calls the HandleColors function when you select it, and that function places a check mark next to the selected item, and clears the check for all other items. (Of course, in real life, you’d want the HandleColors routine to also perform some action in response to the menu selection!)

Sub CreateColors()
  Dim cbp As CommandBarPopup
  Dim varColors As Variant
  Dim intI As Integer
    
  ' Set up the array of colors.
  varColors = Array("Blue", "Green", "Pink", "Yellow", "White")
        
  ' Create the top-level menu.
  Set cbp = CommandBars("Menu Bar").Controls.Add( _
   msoControlPopup, Temporary:=True)
  cbp.Caption = "&Color"
    
  ' Loop through the array, adding one menu item for
  ' each element of the array.
  With cbp.CommandBar.Controls
    For intI = LBound(varColors) To UBound(varColors)
      With .Add(msoControlButton)
        .Caption = varColors(intI)
        .OnAction = "=HandleColors()"
      End With
    Next intI
  End With
End Sub

Public Function HandleColors()
  Dim strCaption As String
  Dim cbc As CommandBarControl
  Dim cbcItem As CommandBarControl
    
  ' Get the selected control, and store its caption.
  Set cbc = CommandBars.ActionControl
  strCaption = cbc.Caption
    
  ' Loop through all the controls in the CommandBar
  ' object that's the parent of the selected control.
  For Each cbcItem In cbc.Parent.Controls
    With cbcItem
      ' Check the selected item, uncheck all the rest.
      If .Caption = cbc.Caption Then
        .State = msoButtonDown
      Else
        .State = msoButtonUp
      End If
    End With
  Next cbcItem
End Function

If you attempt to set the State property for a built-in control, all you’ll get is a runtime error for your efforts.

Changing the Text of a Menu Item

Using the Windows API, you would have called the ModifyMenu and DrawMenuBar functions in order to modify the text of a menu item (after you’d retrieved a handle to the parent menu). Using CommandBars, change the Caption property (and perhaps the ToolTipText, ShortCutText,  and DescriptionText properties, as well) of any control.

For example, to modify the text of the Edit|Delete menu item, you could write code like this:

With CommandBars("Menu Bar"). _
 Controls("Edit").CommandBar.Controls("Delete")
    .Caption = "Remove"
    .ToolTipText = "Remove the selected item"
End With

Deleting a Menu Item

To delete a menu item, use the Delete method of a CommandBarControl, even a control on the main menu bar. (If the control is a CommandBarPopup control and has a menu hanging off it, the Delete method for the item will delete its child menu as well.) The Delete method replaces calls to the RemoveMenu API function.

Items to note:

• Think twice before deleting a menu item. It’s much easier in the long run to set a control’s Visible property to False instead. That way, if you need to show the item again, you don’t need to recreate it. If your intent is to delete it from one place and insert it at another, consider the Move method instead.
  
  
• If you delete a built-in item by mistake, you can use the Reset method of a CommandBar object to set the menus back the way Access created them.

Displaying a Popup Menu

Access makes it easy to assign a Shortcut menu to any control or form, but what if you want to pop up a menu at any location, at any time? The Windows API made this possible, though it was quite tricky to do it in previous versions of Access – it required a lot of calculations, and creating a menu that didn’t appear as part of the menu bar was quite a kludge.

CommandBars make this trivial: you can use the ShowPopup method of a popup CommandBar object (that is, a CommandBar object whose Style property has been set to 2 (msoBarTypePopup)), specifying the location, if you like.  If you don’t specify the location, the menu will appear at the current mouse location.

The following code example uses the ShowPopup method of a CommandBar object to display the popup menu at either the current or a specific location:

Function TrackPopupMenu(cbr As CommandBar, _
 Optional X As Variant, Optional Y As Variant)
  If cbr.Type = msoBarTypePopup Then
    If IsMissing(X) And Not IsMissing(Y) Then
      cbr.ShowPopup , Y
    ElseIf IsMissing(Y) And Not IsMissing(X) Then
      cbr.ShowPopup X
    ElseIf IsMissing(X) And IsMissing(Y) Then
      cbr.ShowPopup
    Else
      cbr.ShowPopup X, Y
    End If
  End If
End Function

To pop up the Database Container menu at the current vertical position, but at the left edge of the screen, you could call code like this:

TrackPopupMenu CommandBars("Database Container"), 0

Some Final Thoughts

This document touched but a tiny portion of the functionality provided by the CommandBar object model. If you want to work with Office menus, take the time to dig through the Online Help topics and the lists of properties and methods in the Object Browser. You’ll find much more flexibility in the new objects than was ever possible using the Windows API directly, and the code you’ll write will be much cleaner. Yes, it’s true, you’ll need to modify all your code that uses the Windows API to manipulate menus, but the final outcome will be simpler to read, maintain, and modify.

Excerpted from:

Access 97 Developer’s Handbook

Paul Litwin, Ken Getz, and Mike Gilbert
© 1996 Sybex, Inc.