Exposing Add-Ins Through Menus and Toolbars

After you set up the basic COM add-in project, you'll need to determine how the add-in will function in an Office 2000 application. Most add-ins will be exposed, or made available to the end user, through a custom menu or toolbar button. Generally, the add-in adds command bar customizations. The end user clicks on the customized menu or button, which executes some code in the add-in or displays a custom dialog box.

Other add-ins don't have a visible user interface like command bar customizations. Some add-ins will be loaded when the application is started and work in the background, handling events such as the opening, closing, or saving of documents. The examples in this section describe the code that allows your add-in to add a menu item that displays a custom dialog box . You can also set up your add-in to trap events from an Office application such as those described in chapters 5 and 10.

Activating Add-Ins Through Command Bar Customizations

When a COM add-in is loaded in an Office application, the first code that's executed is the code in the OnConnection event procedure. Each Office application initializes its menus and toolbars when the application's initialization sequence executes, and you should place code that customizes the application's command bars in the OnConnection procedure.

The following steps explain how to structure your code so that all command bar customization code is in one place, making the add-in project simpler, more readable, and easier to expand.

Adding a Menu Command When a COM Add-In Loads

When a COM add-in is loaded and the OnConnection procedure executes, your code should first ensure any command bar customizations previously added by your add-in exist. If they don't, the procedure given at the end of the following steps adds the specific customizations to the command bar set.

  1. Switch to your COM add-in project in Visual Basic. On the Project menu, click References to display the References dialog box, then scroll down the list of Available References and select Microsoft Office 9.0 Object Library. Click OK to close the References dialog box.

    2. The Microsoft Office 9.0 Object Library defines the objects, methods, properties, and events that allow you to manipulate menus or toolbars, or to add custom menus and toolbars in an Office application. Chapter 11," Creating Menus and Toolbars," provides a complete description of how to use the CommandBars collection object to customize menus and toolbars.

  2. Click Add Module on the Project menu. In the Properties window, set the Name property of the class module to CmdBarInit.

  3. At the top of the CmdBarInit code module, add the following declarations.

    4. Public HostApp As Object
Dim HostCmdBars As Office.CommandBars
Dim CBEvents As New CmdBarEvents
Const m_sBtn1Caption As String = "Button1"
Const m_sBtn1Tag As String = "Button1"

    The first declaration declares the variable HostApp as the generic type Object; HostApp represents the Office application that the add-in is currently loaded into. (The HostApp variable will be set in the OnConnection procedure in a later step.)

    The variable HostCmdBars is declared as the type CommandBars, which is defined in the Microsoft Office 9.0 Object Library. All Office applications access the CommandBars collection object. Here, the variable is used to store the CommandBars collection object of the Office application that the add-in is currently loaded into. The HostCmdBars variable will be set in the InitCmdBarCustomizations procedure, which is listed in the next step.

    The CBEvents object variable references the class CmdBarEvents. For information on how to add this class, see the "Setting Up the Button Click Event Procedure" section following the current set of steps. The CmdBar-Events class contains command bar event procedures for each custom command bar button added.

    The last two declarations are constants that are used to set the properties of the custom menu command that will be added. The strings are declared as constants at the beginning of the code module, so if they change, you need to change them only in one place in your code.

  4. To determine if any command bar controls added by your add-in already exist, add the following InitCmdBarCustomizations procedure:

    5. Sub InitCmdBarCustomizations()
    GetCommandBars
    If HostCmdBars.FindControls(Tag:=m_sBtn1Tag) _
        Is Nothing Then
        AddMenuCtrls
    Else
        Set CBEvents.CtlBtn1 = HostCmdBars _
            .FindControls(Tag:=m_sBtn1Tag).Item(1)
    End If
End Sub

    The first line of the InitCmdBarCustomizations procedure calls the Sub procedure GetCommandBars, which will be added in the following steps. The GetCommandBars procedure sets the variable HostCmdBars. The If…Then…Else block uses the FindControls method on the CommandBars collection object to determine if any control in the command bar set has the custom string tag equal to the constant m_sBtn1Tag.

    If the FindControls method doesn't find any controls that match the search criteria, it returns a value of Nothing. This causes the If…Then expression to evaluate to True and the function AddMenuCtrls is called. If the FindControls method returns one or more existing controls that match the search criteria, the Click event is set up for the existing control(s).

  5. Add the following AddMenuCtrls procedure (in this example only one custom command bar control—namely, a button—is being added):

    6. Sub AddMenuCtrls()
    Dim ctlBtn As CommandBarButton
    Set ctlBtn = HostCmdBars("Tools").Controls.Add
    With ctlBtn
        .Caption = m_sBtn1Caption
        .Tag = m_sBtn1Tag
        Set CBEvents.CtlBtn1 = ctlBtn
    End With
End Sub

  6. Add the following function at the end of the CmdBarInit code module.

    7. Sub GetCommandBars()
    Select Case HostApp.Name
        Case "Outlook"
            Set HostCmdBars = HostApp _
                .ActiveExplorer.CommandBars
        Case Else
            Set HostCmdBars = HostApp.CommandBars
    End Select
End Sub

    This example assigns the HostCmdBars variable to the CommandBars collection object in an Office application. This lets you retrieve the collection once and store it globally for use by all the code in your add-in that manipulates the command bars. In Outlook, the CommandBars collection object can be retrieved through the active Explorer window (or any Explorer object). In all other Office applications, the CommandBars collection object is retrieved through the Application object.

  7. In the Project Explorer, select the ConnectWord project item and click the View Code button. In the ConnectWord code module, add the following lines to the OnConnection procedure:

    8. Set HostApp = Application
InitCmdBarCustomizations

    When Office calls the OnConnection procedure, an instance of the Application object (of the application into which the add-in is being loaded) is passed in. The Application object is specified in the first argument of the OnConnection procedure. The name of the argument is Application and it is declared as the generic type Object.

    The Set statement sets the public variable HostApp, which was declared in the CmdBarInit code module, to the Application object passed into the OnConnection procedure. The second line is the call to the InitCmdBarCustomizations procedure, which was added in the CmdBarInit code module.

Setting Up the Button Click Event Procedure

The following steps describe how to set up the Click event procedure. Once set up, the procedure will be called every time a specific command bar button is clicked by the user, allowing code you add in the Click event procedure to be executed.

  1. On the Project menu, click Add Class Module. Then set the Name property of the class module to CmdBarEvents in the Properties window.

  2. At the top of the CmdBarEvents class module, add the following declaration:

    3. Public WithEvents CtlBtn1 As Office.CommandBarButton

  3. In the Object drop-down list on the class module, click CtlBtn1.

    4. This inserts the Click event procedure for the command bar button.

  4. Add the following line to the Click event procedure:

    5. MsgBox Ctrl.Caption

  5. On the File menu, click Make BasicCOMAddIn.dll.

  6. Start Word. Select the Microsoft Office Basic COM Add-In for Word add-in check box in the COM Add-Ins dialog box, and then click OK.

  7. Click OK to dismiss the message box with the text "Add-in connected to: Microsoft Word." On the Tools menu, click the custom button Button1. The message box with the caption "Button1" appears.

  8. Click OK to dismiss the message box. Click Customize on the Tools menu. While in command bar custom-ization mode, hold down the CTRL key, and then click and drag Button1 on the Tools menu to any other visible toolbar or menu. Repeat this step, and then click Close on the Customize dialog. There are now a few copies of the custom button Button1 in the command bar set.

  9. Click any instance of the custom button Button1 in the command bar set. No matter which copy is clicked, the same code is executed and a message box is displayed.

Displaying a Custom Dialog Box

If you scroll through the Insert, Format, or Tools menus on any Office application, you'll notice that a dialog box appears whenever you click a menu command that contains an ellipsis (...) in its caption string. To follow this standard Windows user-interface design guideline, in the following steps you add the code to the Click event procedure associated with the custom menu item that displays that dialog box. (That is, you add an ellipsis to the custom menu item added by the COM add-in.)

Add a Custom Dialog Box and Load It Through the Click Event

  1. On the Project menu in the COM add-in project, click Add Form. The Add Form dialog box should appear. If the Add Form dialog box doesn't appear, click Options on the Tools menu and in the Options dialog box click the Environment tab. In the Show Templates For group, select the Forms check box and click OK. Then click Add Form on the Project menu again.

  2. In the Add Form dialog box, click Dialog in the New tab and click Open. In order to make the new form represent a typical dialog box, the following properties are automatically set:

    3. Property Value
    BorderStyle 3 – Fixed Dialog
    MinButton False
    MaxButton False
    ShowInTaskbar False
    WhatsThisButton False

  3. Double-click the Dialog item in the Project Explorer to make the form active, and then double-click the OK button on the new form. In the code module, add the code Unload Me to the OKButton_Click event procedure.

  4. In the Procedures drop-down list, click CancelButton and add the same code, Unload Me, in the CancelButton_Click procedure.

  5. Double-click the CmdBarEvents item in the Project Explorer to make the code module active, and then add the following line to the CtlBtn1_Click procedure:

    6. Dialog.Show Modal:=vbModal

    The Show method takes two arguments. The Modal argument indicates whether a form should be displayed as modal or modeless. Dialog boxes are generally displayed as modal, which means that the user can't interact with the Office application until the dialog box has been dismissed.

NOTE
Although the Modal argument in Visual Basic is optional and the default form display for the Show method is modal, you should explicitly set the Modal argument to vbModal when displaying a form from a COM add-in. If you don't explicitly set the argument, the form will be displayed as modeless and it will not appear in front of the Office application window. In this case, you need to set the parent window of the modeless to the Office application window.

  1. In the CtlBtn1_Click procedure, add an apostrophe (') to the beginning of the line MsgBox Ctrl.Caption to prevent the message box from being displayed.

  2. Double-click the CmdBarInit item in the Project Explorer to make the code module active and add an ellipsis to the string constant m_sBtn1Caption that was declared at the beginning of the module. The constant declaration should appear as follows:

    3. Const m_sBtn1Caption As String = "Button1..."

  3. Switch to Word and in the COM Add-Ins dialog box, clear the item Microsoft Office Basic COM Add-In for Word if it's selected. Click OK to dismiss the COM Add-Ins dialog box and click Exit on the File menu to exit Word.

  4. Switch back to your Visual Basic project and on the File menu, click Save Project to save the new project items. On the File menu, click Make BasicCOMAddIn.dll, and in the Make Project dialog box, click OK.

  5. Start Word. Select the Microsoft Office Basic COM Add-In For Word add-in check box in the COM Add-Ins dialog box, and then click OK.

  6. Click Button1 on the Tools menu to display the custom dialog box.

    7. If you chose to run the project in Visual Basic rather than compile the project and then let the Office application load the compiled project, the resulting dialog box will be displayed in front of the Visual Basic window. This is similar to the custom dialog box behavior that occurs when you debug your Visual Basic code. If you compiled the add-in into a .dll without running the Visual Basic project, custom dialog boxes and message boxes will appear in front of the Office application.

Deleting Command Bar Customizations

When a COM add-in is loaded and the OnConnection procedure is executed, code that you added in the steps for "Adding a Menu Command When a COM Add-In Loads" determines if any command bar customizations added by your add-in already exist. If they don't, a procedure that adds the specific customizations to the command bar set is executed.

When an add-in is unloaded and the OnDisconnection procedure executes, your code conversely should determine whether command bar customizations should be removed. Notice that the following steps are similar to the steps for adding menu commands when a COM add-in loads.

  1. In the Project Explorer window (with the ConnectWord project item selected), click the View Code button so the code module is active. In the OnDisconnection procedure, add the following code:

    2. If RemoveMode = ext_dm_UserClosed Then
    DeleteMenuCtrls
End If

    The first argument passed to the OnDisconnection event procedure is the RemoveMode value. The two values passed in are ext_dm_Host-Shutdown and ext_dm_UserClosed. The value of ext_dm_UserClosed is passed into the RemoveMode argument when you unload an add-in through the COM Add-Ins dialog box. When the add-in is cleared in the COM Add-Ins dialog box, the add-in is no longer available. Although the add-in is still registered, you should remove command bar customizations from the command bar set when the user unloads the add-in.

    The value of ext_dm_HostShutdown is assigned to the RemoveMode argument when the Office application unloads the add-in while the application is exiting. In this case, you should retain command bar customizations. This way, the customizations will already be present the next time the Office application is started, without executing the code to add them. (In most cases, the application will unload add-ins when you exit it.)

  2. Double-click the CmdBarInit item in the Project Explorer to make the code module active. Add the following DeleteMenuCtrls procedure at the CmdBarInit code module.

    3. Sub DeleteMenuCtrls()
    Dim ctlItem As CommandBarControl
    Dim ctlColl As CommandBarControls
    
    Set ctlColl = HostCmdBars.FindControls(Tag:=m_sBtn1Tag)
        
    If Not ctlColl Is Nothing Then
        For Each ctlItem In ctlColl
            ctlItem.Delete
        Next ctlItem
    End If
End Sub

    The DeleteMenuCtrls procedure uses the FindControls method to return all copies of the custom menu command in the command bar. The FindControls method allows you to support the scenario in which the user has moved your custom menu command from its original position or has copied the custom menu command to another menu. The section "The FindControls Method" in chapter 11 describes more about the FindControls method.

  3. Switch to Word and in the COM Add-Ins dialog box, clear the item Microsoft Office Basic COM Add-In for Word if it's selected. Click OK to dismiss the COM Add-Ins dialog box and click Exit on the File menu to exit Word.

  4. Switch back to your Visual Basic project, click Make BasicCOMAddIn.dll on the File menu and in the Make Project dialog box, click OK.

  5. Start Word. Select the Microsoft Office Basic COM Add-In for Word check box in the Make Project dialog box, and then click OK. Display the COM Add-Ins again and clear the Microsoft Office Basic COM Add-In for Word check box in the COM Add-Ins dialog box. All instances of the custom menu command Button1 are removed.

Loading Add-Ins on Demand Through Command Bars

Add-ins that just add customizations to a command bar and execute code only when a custom menu item or toolbar is clicked don't need to be loaded when the Office application starts. Instead, you can have the add-in load on demand—that is, when the user clicks on a customization, the add-in loads into memory immediately following the click event.

To add your custom commands after your add-in is installed, you need to set the initial load behavior of the COM add-in so that it loads the next time the Office application starts. Office then sets the load behavior registered for the add-in so the custom command demand-loads the add-in the next time the Office application is started.

Because your add-in is loaded only on demand, it does not get loaded when the Office application is started, and thus does not affect the time it takes to start an Office application. Add-ins that are loaded when the Office application is started increase the time it takes to start the Office application.

Load an Add-In at Next Startup Only

  1. In your COM add-in project, double-click the ConnectWord project item in the Project Explorer to make the Add-in Designer window active.

  2. In the Initial Load Behavior drop-down list, select Load At Next Startup Only.

  3. In the Project Explorer window, double-click the code module CmdBarInit to make it the active module. In the procedure AddMenuCtrls, add the following Select Case statement so that the procedure appears as follows:

    4. Sub AddMenuCtrls()
    Dim ctlBtn As CommandBarButton
    Set ctlBtn = HostCmdBars("Tools").Controls.Add
    With ctlBtn
        .Caption = m_sBtn1Caption
        Select Case HostApp.Name
            Case "Microsoft Word"
                .OnAction = "!<BasicCOMAddIn.ConnectWord>"
        End Select
        .Tag = m_sBtn1Tag
        Set CBEvents.CtlBtn1 = ctlBtn
    End With
End Sub

    When an add-in is set to load on demand, Office needs a way to associate a command bar control with a COM add-in. Office determines the association by evaluating the OnAction string assigned to the custom command bar control. In the Select Case statement above, the OnAction string is assigned to the programmatic identifier (or ProgID) of the COM add-in when it is loaded into Word. In the section "Adding Another Office Application to the Add-In" after the following steps, the ProgID will be BasicCOMAddIn.ConnectExcel and assigned to the OnAction property when the add-in is loaded into Excel. (The ProgID allows you to uniquely identify your COM add-in and distinguish it from other COM add-ins.)

  4. Switch to Word and exit the application. On the File menu, click Make BasicCOMAddIn.dll. In the Make Project dialog box, click OK and then start Word. The COM add-in should be loaded and Button1 should be added to the Tools menu.

  5. Display the COM Add-Ins dialog box and select Microsoft Office Basic COM Add-In for Word in the list. At the bottom of the dialog box you'll see the Load Behavior label and the current load status of the add-in. The load status will be "Load on Demand, (currently loaded)."

  6. Click OK to dismiss the COM Add-Ins dialog box (do not clear the add-in in the list).

  7. Click Button1 on the Tools menu to ensure that the click event is fired and displays the custom dialog box. Click OK.

  8. Click Exit on the File menu and restart Word. Display the COM Add-Ins dialog box by clicking the COM Add-Ins command.

    9. See steps 2 and 3 in the section "Test the COM Add-In" to learn how to find the COM Add-Ins command. In the COM Add-Ins dialog box, with Microsoft Office Basic COM Add-In for Word selected in the list, the load status will be "Load on Demand, (not currently loaded)."

  9. Click OK to dismiss the COM Add-Ins dialog box. Click Button1 on the Tools menu. Office loads the add-in in Word, because this is the first time the add-in is needed. The add-in has now been loaded on demand and the custom dialog box is displayed.

  10. Click OK to dismiss the custom dialog box, and then display the COM Add-Ins dialog box again.

    11. With Microsoft Office Basic COM Add-In for Word selected in the list, the current load status will be "Load on Demand, (currently loaded)."

The user doesn't have to explicitly load the add-in through the COM Add-Ins dialog box. Once the add-in is set up on the user's machine, the next time the user starts Word, the COM add-in loads and Button1 is added. Thereafter, the next time the user starts the application, the add-in isn't loaded at startup, but only when the user clicks the custom command on the Tools menu.

NOTE
For information on using the Package and Deployment wizard to deploy your add-in onto a user's machine, see the section "Packaging and Deploying Your COM Add-In" near the end of this chapter.

Adding Another Office Application to the Add-In

  1. Click the Add Addin Class command on the Project menu. In the Properties window, change the Name property of the Designer project item from AddinDesigner1 to ConnectExcel. Set the Public property to True. When you set the Public property of the Add-in Designer to True, a message box is displayed. Click OK.

  2. Add the following settings to the fields in the General tab of the ConnectExcel window:

    3. Add-in Designer window Setting
    Addin Display Name edit box Add the text "Microsoft Office Basic COM Add-In for Excel."
    Application drop-down Microsoft Excel
    Application Version drop-down Microsoft Excel 9.0
    Initial Load Behavior drop-down None

  3. In the Project Explorer window, with the ConnectExcel project item selected, click the View Code button at the top left of the Project Explorer window. In the ConnectExcel code window, select AddinInstance in the Object drop-down list of the module.

  4. In the AddinInstance_OnConnection procedure, which is added auto-matically after AddinInstance is selected in the Object drop-down list, add the following lines:

    5. Set HostApp = Application
InitCmdBarCustomizations

  5. In the Procedure drop-down list of the class module, select OnDisconnection. In the AddinInstance_OnDisconnection procedure, add the following code.

    6. If RemoveMode = ext_dm_UserClosed Then
    DeleteMenuCtrls
End If

  6. In the Project Explorer window, double-click the code module CmdBarInit to make it the active module. In the procedure AddMenuCtrls, add a new Case expression for Excel so that the Select Case statement appears as shown below.

    7. Select Case HostApp.Name
    Case "Microsoft Word"
        .OnAction = "!<BasicCOMAddIn.ConnectWord>"
    Case "Microsoft Excel"
        .OnAction = "!<BasicCOMAddIn.ConnectExcel>"
End Select

    In the Select Case statement above, the OnAction string is assigned to the programmatic identifier (or ProgID) of the COM add-in when it is loaded into Excel. When the add-in is loaded into Excel, the ProgID is BasicCOMAddIn.ConnectExcel.

  7. On the File menu, click Save Project to save the ConnectExcel project file. Name the file ConnectExcel.dsr.

  8. Switch to Word and exit the application. On the File menu in the Visual Basic window, click Make BasicCOMAddIn.dll. In the Make Project dialog box, click OK.

  9. Start Excel and click Customize on the Tools menu to display the Customize dialog box.

  10. In the Customize dialog box, click the Commands tab and select Tools from the Categories list box. Scroll down the Commands list box next to the Categories list box until the COM Add-Ins command is in view.

  11. Drag the COM Add-Ins command to the end of the main menu bar in Excel (to the right of the Help menu).

  12. Click Close to dismiss the Customize dialog box. Click the COM Add-Ins command and in the COM Add-Ins dialog box select Microsoft Office Basic COM Add-In for Excel. Click OK. Button1 is added to the bottom of the Tools menu.

  13. Click Button1 to display the custom dialog box.

  14. Click OK, and then click the COM Add-Ins command again. Clear the Microsoft Office Basic COM Add-In for Excel option and click OK. The add-in is unloaded and the custom menu item Button1 is removed from the Tools menu. Exit Excel.