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

  • Article

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

If the user runs tools in your add-in by clicking a command bar control (toolbar button or menu item), you can include code to display or to create the command bar and control when the add-in loads and to hide or to remove the command bar and control when it unloads. Although it might seem to be more effort, creating and destroying the command bar from within your code gives you greater control over when the command bar is displayed than only 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 Microsoft® Word, or Auto_Open for Microsoft® Excel and Microsoft® PowerPoint®.

**Note   **These code examples do not show error handling. For example, the procedures do not handle the case when another add-in might have a command bar with the same name.

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
...Else
      ' Make sure the existing commandbar is visible
      cbrWiz.Visible = True
   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

See Also

Building Application-Specific Add-ins | Word Add-ins | Excel Add-ins | PowerPoint Add-ins | Access Add-ins | Controlling Word, Excel, and PowerPoint Add-ins from Code | Securing an Access, Excel, PowerPoint, or Word Add-in's VBA Project