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