ACC97: How to Create Command Bars by Using Visual Basic Code (159692)

MORE INFORMATION

In Microsoft Access 97, toolbars, shortcut menus, and menu bars are combined into a single CommandBars collection that you can control programmatically by using Visual Basic for Applications. You can modify any Microsoft Access built-in menu bars or toolbars, and you can create and modify custom toolbars, menu bars, and shortcut menus.

In order to program with command bars, you must set a reference to the Microsoft Office 8.0 Object Library. To do so, open a module in Design view and click References on the Tools menu. Click to select the box next to Microsoft Office 8.0 Object Library. If you do not see that reference in the Available References box, click Browse to search your hard disk for the Mso97.dll file, which is installed by default into the C:\Program Files\Microsoft Office\Office folder.

The following examples create command bars programmatically and assign actions to their controls. Example 1 creates a floating toolbar. Example 2 creates a docked menu bar.

Example 1: Creating a Toolbar

Open the sample database Northwind.mdb.

Create a module and type the following procedures:

     '****************************************************************
      ' This procedure creates a new toolbar.
      '****************************************************************
      Sub AddNewCB()
         Dim CBar As CommandBar, CBarCtl As CommandBarControl
         On Error GoTo AddNewCB_Err

         ' Create a new floating toolbar and make it visible.
         Set CBar = CommandBars.Add(Name:="Sample Toolbar", Position:= _
            msoBarFloating)
         CBar.Visible = True

         ' Create a button with text on the bar and set some properties.
         Set CBarCtl = CBar.Controls.Add(Type:=msoControlButton)
         With CBarCtl
            .Caption = "Button"
            .Style = msoButtonCaption
            .TooltipText = "Display Message Box"
            .OnAction = "=MsgBox(""You pressed a toolbar button!"")"
         End With

         ' Create a button with an image on the bar and set some
         ' properties.
         Set CBarCtl = CBar.Controls.Add(Type:=msoControlButton)
         With CBarCtl
            .FaceId = 1000
            .Caption = "Toggle Button"
            .TooltipText = "Toggle First Button"
            .OnAction = "=ToggleButton()"
         End With

         ' Create a combo box control on the bar and set some properties.
         Set CBarCtl = CBar.Controls.Add(msoControlComboBox)
         With CBarCtl
            .Caption = "Drop Down"
            .Width = 100
            .AddItem "Create Button", 1
            .AddItem "Remove Button", 2
            .DropDownWidth = 100
            .OnAction = "=AddRemoveButton()"
         End With
         Exit Sub
         AddNewCB_Err:
         MsgBox "Error " & Err.Number & vbCr & Err.Description
         Exit Sub
      End Sub

      '****************************************************************
      ' This procedure is called from a button on the toolbar.
      ' It toggles the Visible property of another button on the bar.
      '****************************************************************
      Function ToggleButton()
         Dim CBButton As CommandBarControl
         On Error GoTo ToggleButton_Err
         Set CBButton = CommandBars("Sample Toolbar").Controls(1)
         CBButton.Visible = Not CBButton.Visible
         Exit Function
         ToggleButton_Err:
         MsgBox "Error " & Err.Number & vbCr & Err.Description
         Exit Function
      End Function

      '****************************************************************
      'This procedure is called from a combo box on the toolbar
      'It adds a button to the bar or removes it
      '****************************************************************
      Function AddRemoveButton()
         Dim CBar As CommandBar, CBCombo As CommandBarComboBox
         Dim CBNewButton As CommandBarButton
         On Error GoTo AddRemoveButton_Err
         Set CBar = CommandBars("Sample Toolbar")
         Set CBCombo = CBar.Controls(3)
         Select Case CBCombo.ListIndex
            'If Create Button is selected, create a button on the bar
            Case 1
               Set CBNewButton = CBar.Controls.Add(Type:=msoControlButton)
               With CBNewButton
                  .Caption = "New Button"
                  .Style = msoButtonCaption
                  .BeginGroup = True
                  .Tag = "New Button"
                  .OnAction = "=MsgBox(""This is a new button!"")"
               End With
            ' Find and remove the new button if it exists.
            Case 2
               Set CBNewButton = CBar.FindControl(Tag:="New Button")
               CBNewButton.Delete
         End Select
         Exit Function
         AddRemoveButton_Err:
         ' If the button does not exist.
         If Err.Number = 91 Then
            MsgBox "Cannot remove button that does not exist!"
            Exit Function
         Else
           MsgBox "Error " & Err.Number & vbCr & Err.Description
           Exit Function
         End If
         End Function

To test this function, type the following line in the Debug window, and then press ENTER:
AddNewCB
Note that a floating toolbar appears on the screen.
Click the word "Button" on the toolbar and note the message box that appears.
Click the button with the light bulb image and note that "Button" disappears. Click it again and "Button" reappears.
Select Create Button in the combo box and note that a button called New Button is added to the toolbar. Select Remove Button in the combo box and note that New Button is removed from the toolbar.

Example 2: Creating a Menu Bar

Open the sample database Northwind.mdb.