Community Pick: Many members of our community have endorsed this article.
Editor's Choice: This article has been selected by our editors as an exceptional contribution.

Understanding and using CommandBars (ShortCut menus), Part 1

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Published:
Updated:
Right-Click shortcut (PopUp) menus are an integral part of the Windows environment and users expect to see them in their custom applications.  However, there are times when you don't want to present your users with the standard set of shortcut options available in the current context of your application.  In those instances, you can either manipulate those that already exist, or you can create your own.

In Access versions prior to 2007 there was a convenient method for creating shortcut menus, but that method was eliminated in Access 2007.  If you are fortunate enough to still have a copy of Access 2003 (or an earlier version), you can still create custom toolbars using that method and then import them into your 2007+ application using the External Data import method, by checking the "Menus and Toolbars" Import option.Import toolbarI will expand on the concept of creating your own toolbars in Part II of this series, for now lets focus on understanding the existing CommandBars, specifically the shortcut menus (or popup toolbars).

When I type:
?application.commandbars.Count

Open in new window

in my immediate window, I get 199.  Some of these are not shortcut menus, so the actual number in Access 2007 is somewhere around 130.  You can generate a list of those or dump those into table with a subroutine similar to:
Public Sub CbrShortCutMenus(Optional SearchFor As String)
                      
                          Dim cbr As Object
                          Dim intCount As Integer
                          
                          For Each cbr In Application.CommandBars
                              If cbr.Type = 2 Then
                                  If SearchFor = "" Then SearchFor = " "
                                  If InStr(cbr.Name & " ", SearchFor) > 0 Then Debug.Print cbr.Name
                              End If
                          Next
                      
                      End Sub

Open in new window

This routine generates a list of all the shortcut menus.  If you include the optional [SearchFor] argument it will only list those where the search string is included somewhere in the commandbars name (there are 20 that contain the word 'Form' somewhere in the name, 17 contain the word 'Datasheet').  However, that subroutine only gives you the names of the menus, and the name is not always self explanatory.  If you want to see what menu items are actually in that shortcut menu, you can use the ShowPopUp method of the CommandBar object.
Commandbars("Print Preview Popup").ShowPopup

Open in new window

However, this method will only show you the items in the menu that are visible in the context where it is used; some may be disabled and some may be hidden.  Another method is to simply loop through all of the controls associated with the command bar and print them out or store them.  The following code will print a list of the menu items, their ID value, and whether they are visible in the current context to the debug window.
Public Sub CbrMenuItems(CommandBarName As String)
                      
                          Dim intLoop As Integer
                          Dim ctrl As Control
                          
                          On Error Resume Next
                          For intLoop = 1 To CommandBars(CommandBarName).Controls.Count
                              Debug.Print CommandBars(CommandBarName).Controls(intLoop).Caption;
                              Debug.Print CommandBars(CommandBarName).Controls(intLoop).ID;
                              Debug.Print CommandBars(CommandBarName).Controls(intLoop).Visible
                          Next
                          
                      End Sub

Open in new window

I included the On Error Resume Next line because not every commandbar control contains an ID property and if you try to print that property for a control that does not contain it, Access will raise an error.

Access will automatically enable/disable or hide the various controls in these commandbars based on the context in which it is being used, but occassionally you may want to customize this functionality.

I have a client that likes the ability to view their data in a datasheet view, allowing each user to customize their view of the data by hiding and repositioning columns.  However, only certain individuals have permissions to delete records in these tables.  I normally just set the forms Allow Deletions property to False in the Form_Load event for those individuals who do not have this permission, but when you do so, the "Delete Row" option is still displayed (disabled) in the Form Datasheet Row commandbar.  To hide that control, I simply set that controls visible property to false using the following code.
CommandBars("Form Datasheet Row").Controls(2).visible = False

Open in new window

Note: don't forget to reset that property to True when you close or unload the datasheet.

In this example, the value 2 relates to the index value of the control within the commandbar.  Another alternative, if you don't know the index value of the control or if you want your code to be more readable, is to use the FindControl method of the CommandBar object using a syntax of:
Commandbars(CommandBarName).findcontrol([type], [Id], [Tag], [Visible], [Recursive])

Open in new window

This method returns a control object which you can set to a control variable:
Set ctrl = Commandbars().FindControl(ID:=644)

Open in new window

that you can use within your code to reference various properties of the control.  To hide the "Delete Row" option from the "Form Datasheet Row" popup, you would use:
Commandbars().FindControl(ID:=644).Visible = false

Open in new window

All of the built-in commandbar controls have an ID property which you can use to select a commandbar control.  

Because you can control the visibility of commandbar controls through VBA and can also add new items to one of the built-in commandbars, you can gain complete control over the built in commandbars.  CAUTION: if you modify commandbar control properties, you must ensure that you reset those properties or they will persist throughout the remainder of that Access session.  I generally use the Form_Load and Form_Close events of my forms to make these modifications.

In my next article, Part II, I'll discuss how to create your own commandbars and how to modify existing commandbars to add your own functionality to the existing.
9
14,971 Views
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.