Microsoft Office 2000/Visual Basic Programmer's Guide

Adding and Removing Command Bars for Word, Excel, and PowerPoint Add-ins

If the user runs your add-in by clicking a command bar control (toolbar button or menu item), you can include code to display or create the command bar and control when the add-in loads, and hide or remove the command bar and control when it unloads. Although it may seem like more effort, creating and destroying the command bar from within your code gives you greater control over when the command bar is displayed than simply storing the command bar in the add-in file.

To create the command bar when the add-in is loaded, add code to the procedure that runs when the add-in is loaded: AutoExec for Word, or Auto_Open for Excel and PowerPoint. First check whether the command bar already exists. If it does not, create it and add a button that runs a Sub procedure, as shown in the following example:

Private Const CBR_INSERT As String = "Insert Info Wizard"
Private Const CTL_INSERT As String = "Insert Info"
Sub AutoExec()
   Dim cbrWiz       As CommandBar
   Dim ctlInsert    As CommandBarButton
   On Error Resume Next
   ' Determine whether command bar already exists.
   Set cbrWiz = CommandBars(CBR_INSERT)
   ' If command bar does not exist, create it.
   If cbrWiz Is Nothing Then
      Err.Clear
      Set cbrWiz = CommandBars.Add(CBR_INSERT)
      ' Make command bar visible.
      cbrWiz.Visible = True
      ' Add button control.
      Set ctlInsert = cbrWiz.Controls.Add
      With ctlInsert
         .Style = msoButtonCaption
         .Caption = CTL_INSERT
         .Tag = CTL_INSERT
         ' Specify procedure that will run when button is clicked.
         .OnAction = "ShowForm"
      End With
   End If
End Sub

To delete the command bar when the add-in is unloaded, add code to the procedure that runs when the add-in is unloaded: AutoExit for Word, or Auto_Close for Excel and PowerPoint. The following procedure deletes the command bar created in the previous example:

Sub AutoExit()
   On Error Resume Next
   ' Delete command bar, if it exists.
   CommandBars(CBR_INSERT).Delete
End Sub

The AutoExec and AutoExit procedures shown in the preceding examples are available in the modWizard module in InsertInfo.dot in the ODETools\V9\Samples\OPG\Samples\CH11 subfolder on the Office 2000 Developer CD-ROM.