ACC97: How to Create Command Bars Using Visual Basic Code

Last reviewed: January 28, 1998
Article ID: Q159692
The information in this article applies to:
  • Microsoft Access 97

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article contains examples that show you how to programmatically add and remove items on command bars, specifically a menu bar and a toolbar.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

NOTE: A demonstration of the techniques used in this article can be seen in the sample file, FrmSmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q175066
   TITLE     : ACC97: Microsoft Access 97 Sample Forms Available on MSL

MORE INFORMATION

In Microsoft Access 97, toolbars, shortcut menus, and menu bars are combined into a single CommandBars collection which you can control programmatically 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 drive 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

  1. Open the sample database Northwind.mdb.

  2. 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
    
    

  3. 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.

  4. Click the word "Button" on the toolbar and note the message box that appears.

  5. Click the button with the light bulb image and note that Button disappears. Click it again and Button reappears.

  6. 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

  1. Open the sample database Northwind.mdb.

  2. Create a module and type the following procedures:

          '****************************************************************
          ' This procedure creates a new menu bar.
          '****************************************************************
    
          Sub AddNewMB()
             Dim MBar As CommandBar, MBarCtl As CommandBarControl
             Dim MBarPopup As CommandBarPopup, MBarSubCtl as CommandBarControl
             On Error GoTo AddNewMB_Err
    
             ' Create a new menu bar and dock it on the left.
             Set MBar = CommandBars.Add(Name:="Sample Menu Bar", Position:= _
                msoBarLeft, MenuBar:=True, Temporary:=False)
             ' Make the menu bar visible.
             MBar.Visible = True
             ' Prevent users from undocking the menu bar.
             MBar.Protection = msoBarNoMove
    
             ' Create a popup control on the bar and set its caption.
             Set MBarCtl = MBar.Controls.Add(Type:=msoControlPopup)
             MBarCtl.Caption = "Displa&y"
    
             ' Create 2 controls on the Display popup and set some properties.
             Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)
             With MBarSubCtl
                .Style = msoButtonIconAndCaption
                .Caption = "E&nable ClickMe"
                .FaceId = 59
                .OnAction = "=ToggleClickMe()"
                .Parameter = 1
                .BeginGroup = True
             End With
             Set MBarSubCtl = MBarCtl.Controls.Add(Type:=msoControlButton)
             With MBarSubCtl
                .Style = msoButtonIconAndCaption
                .Caption = "Di&sable ClickMe"
                .FaceId = 276
                .OnAction = "=ToggleClickMe()"
                .Parameter = 2
                .BeginGroup = True
             End With
    
             ' Add another control to the menu bar.
             Set MBarCtl = MBar.Controls.Add(Type:=msoControlButton)
             With MBarCtl
                .BeginGroup = True
                .Caption = "&ClickMe"
                .Style = msoButtonCaption
                .OnAction = "=MsgBox(""You clicked ClickMe"")"
             End With
    
             ' Add a control to make this menu bar invisible and bring back
             ' the system menu bar.
             Set MBarCtl = MBar.Controls.Add(Type:=msoControlButton)
             With MBarCtl
                .BeginGroup = True
                .Caption = "&Show Northwind Menu Bar"
                .Style = msoButtonCaption
                .OnAction = "=SampleMenuDisable()"
             End With
             Exit Sub
             AddNewMB_Err:
             MsgBox "Error " & Err.Number & vbCr & Err.Description
             Exit Sub
          End Sub
    
          '****************************************************************
          ' This procedure uses the Parameter property of a command bar
          ' control to execute a different action depending on which item
          ' you click on a popup menu.
          '****************************************************************
          Function ToggleClickMe()
             Dim MyMenu As CommandBar
             Dim MBarClickMe As CommandBarControl
             On Error GoTo ToggleClickMe_Err
             Set MyMenu = CommandBars("Sample Menu Bar")
             Set MBarClickMe = MyMenu.Controls(2)
             ' The ActionControl property of command bars returns the control
             ' whose OnAction property is running this procedure.
             With CommandBars.ActionControl
                Select Case .Parameter
                   Case 1
                      MBarClickMe.Enabled = True
                   Case 2
                      MBarClickMe.Enabled = False
                End Select
             End With
             Exit Function
             ToggleClickMe_Err:
             MsgBox "Error " & Err.Number & vbCr & Err.Description
             Exit Function
          End Function
    
          '****************************************************************
          ' This function restores the original menu bar. Because there can
          ' only be one system menu bar, you must hide the sample menu bar
          ' when you want to bring back the previous system menu bar.
          '****************************************************************
          Function SampleMenuDisable()
             Application.CommandBars("Sample Menu Bar").Visible = False
             Application.CommandBars("NorthwindCustomMenuBar").Visible = True
          End Function
    
    

  3. To test this function, type the following line in the Debug window, and then press ENTER.

          AddNewMB
    

    Note that a docked menu bar appears on the left side of the screen.

  4. Click the ClickMe button on the menu and note the message box that appears.

  5. Click the Display button, and then click Disable ClickMe. Note that the ClickMe button is dimmed.

  6. Click the Display button, and then click Enable ClickMe. Note that the ClickMe button is enabled.

  7. Click the Show Northwind Menu Bar button to restore the original system menu bar.

REFERENCES

For more information about the methods and properties of command bars, search the Help Index for "CommandBars collection," or ask the Microsoft Access 97 Office Assistant.

For more information about the methods and properties of command bar controls, search the Help Index for "CommandBarControls collection," or ask the Microsoft Access 97 Office Assistant.

For more information on how to programmatically add and remove items on command bars, please see the following article(s) in the Microsoft Knowledge Base:

   ARTICLE-ID: Q172300
   TITLE     : ACC97: Command Bar Wizard Available on MSL
Keywords          : kbprg kbusage PgmObj UifCmdbar
Version           : 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: January 28, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.