?application.commandbars.Count
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
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
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
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.
CommandBars("Form Datasheet Row").Controls(2).visible = False
Note: don't forget to reset that property to True when you close or unload the datasheet.
Commandbars(CommandBarName).findcontrol([type], [Id], [Tag], [Visible], [Recursive])
This method returns a control object which you can set to a control variable:
Set ctrl = Commandbars().FindControl(ID:=644)
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
All of the built-in commandbar controls have an ID property which you can use to select a commandbar control.
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.
Comments (0)