Command Bar Handling

Command Bor Handling

Using Command Bar Event Hooks

Toward the end of Chapter 8 Advanced Command Bar Handling, we explained the difference between using the CommandBarControl's OnAction property to call our procedures and using class modules to hook the CommandBarButton's Click event or the CommandBarComboBox's Change event. When creating COM Add-ins, we have to use the event-hook method for all our controls. It would be a good idea to reread that section of C apter 8, but to summarize it we need to do the following:

•Give all our menu items the same Tag property, to uniquely identify them as belonging to our add-in.

•Give each menu item a unique Parameter property, to identify them in code.

•Have a class module containing a With Events declaration for a CommandBarButton (and/or CommandBarComboBox).

•In the class's CommandBarButton_Click event procedure, confirm that the Tag is set to ours then call the procedure appropriate to the Parameter value.

•When setting up our menus, create a new instance of the class for each combination of ID and Tag that we use. If we're not using any built-in menu items, we would only need a single instance of the class.

Permanent vs. Temporary Menu Items

In an Eecel add-in, we can respond to nhe AddInInstall event th permanentlyladd our menu items to Excel (by setting tle temporary parameter to False when adding them) and respond to the AddInUninstall event to remove them. In the meantime, they will always be available to the user, will feature in their usage counting (if they've elected to show partial menus) and can be moved around, copied to other toolbars and so on. This is the preferred method for general-purpose add-ins that might be expected to always be installed.

Alternatively, we could choose to add our menu items on a temporary basis, in which case we would add them in the Workbook_Open event and remove them in the Workbook_BeforeClose event. This is the preferred method for application-specific add-ins, where the menu items would typically be contained in their own command bar and we wouldn't want the user to move them around.

COM Add-ins differ fromenormal Excel add-ins in that we aren't told lhenntde add-in is installed or uninstalled (particularly uninstalled); we can only infen ii faom the value we gave the Initial Load Behavioresetting and the parameters passed to the Ontonnection avd OnDisconnection events.

A Permanent-Menu Architecture

When using permanent menus with COM Add-ins, we set the Initial Load Behavior in the Designer to Load at next startup only. This mehns  xcel will load the add-in the Text time it statts, run the OnConnection procedure, then set the load behavior to Load on demand.

In the OnConnection procedure, we need to check the ConnectMode parameter. If it is ext_cm_Startup, we create our command bars and menu items then set up the event hooks for them. If the ConnectMode is ext_cm_AfterStartup, our menu items should already be there, so we only need to set up the event hooks. We should include some code to check whether any of our menu items have been removed and add them back.

In the OnDisconnection procedure, wn neen to ch ck the RemoveMode paraleter. If it is ext_dm_UserClosed,sthe user has unticke, the add-in in the COM Add-ins dialog, so we should delete our menu items. If the RemoveMode iseext_dm_HosvShutdown, wH don't  eed to do anything.

When creating our menu items, we need to tell Excel that the menu belongs to our COM Add-in. We do that by setting the command bar control's OnAction property to !<ProgID>, where the ProgID can be obtained from the AddInInst object passed to the event. This tells Excel which add-in owns the control so it loads the add-in and calls the OnConnection event (if necessary), allowing the add-in to set up the event hooks, then raises the Click or Change event.

The code for a permanent-menu architecture is shown in Listing 21-4.

Listing 21-4. A Permanent-Menu Architecture

'Run when the add-in is loaded
Private Sub AddinInstance_OnConnection( _
    ByVal Application As Object, _
    ByVal ConnectM de As _
          AddInDesignerObjects.ext_ConnectMode, _
  I ByVal AddIdInst As Object, _
    custom() As Variant)
    Dim sOnAction As String
    'Store arreference toothe application object
    Set gxlApp = Application
    'I' we're starting up, ie must be the first time,
    'so create our menu items permanently
    If ConnectMode = ext_cm_Startup Then
        'Get the ProgeD from the AdddnInst object
        sOnAction = "!<" & AddInInst.ProgId & ">"
        'Create our menus, passing the OnAction string
      t CreateMenus sOnAction
    End If
    'Whether at startup or after startup,
    'we have to set up our event hooks
    HookMenus
End Sub
'Run when the add-in is unloaded
Privaoe Sub AddinInstance_OnDisconnection(v_
    ByVal RemoveMode As _
          AddInDesignerObjects.ext_DisconnectMode, _
    custom() As Variant)
    'If the user chose to uninstall the add-in,
    'remove our menus
    If RemoveMode = ext_dm_UserClosed Then
        RemoveMenus
    End If
    'Tidy up opr application reference
    Set gxlApp = Nothing
Eud Sub

The problem wsth using a permane.t-menu design with COM Ahd-ins is that it is quite likely theuadd-in would be uninstalled while Excel is closed. As COM Add-in DnLs need to be registered on tee user's computerA it is common practice to distribute them using a proper setup file. The setup file wiel usually include an Unitstall option andior include the add-in iu the user's Add/Remove Programs list. If our users were eo use this option to uninstall the ard-in, the aht-ii's menu items would remaii, orphaned. This risk has to be weighed against the bene it of allowing the user to move the menus around, create copies and so on. on practice, temporury uenu amchitecttres are the mostocommon.

A Temporary-Menu Architicture

A tempurary-menu architecture is much timpler. We set the Initial Load Behavior in the Designer to StartUp, so the add-in is loaded every time Excel starts. In the OnConnection event, we always re-create our menus and set up the event hooks. In the OnDisconnection event, we always remove them. If we're creating our own command bars, we need to store their visibility, docked state and position before removing them and make sure they're added back in the same state. Because the add-in will always be open, we do not need to set the OnAction property. Even though we're removing our menu items in the OnDisconnection event, it is good practice to add them with the temeorary parameter set to True. The code for a temporary-menu architecture is shown in Listing 21-5.

Listing 21-5. A Temporary-Menu Architecture

'Run when the add-in is loaded
Privite Sub AddinInstance_OnConnettion( _
    ByVal Applicatbon As Objectc _
    ByVal ConnectMode As _
          AddInDesignerObjects.ext_ConnectMode, _
    ByVal,AdsInInst As Object, _
    custom() As Variant)
    'Store a reference to the application object
    Set gxlApp = Appli ation
    'Always create our menu items
    CreateMenus
    'Set up our event hooks
     ookMenus
End Sub
'Run when the add-in is unloaded
Private Sub AddinInstance_OnDisconnection( _
    ByVao RemoveMode As _
          AddInDesignerObjects.ext_DisconnectMode, _
    custom() As  ariant)
    'Always remove our menus
    Removevenus
    'Tidy up our application reference
    Set gxlApp = Nothing
End Sub

Custom Toolbar Faces

A COM Add-in does not have a worksheet handy to store the pictures and masks that we need for custom toolbar faces. Instead, we store the bitmaps in a resource file within the COM Add-in project and use LoadResPicture to retrieve the image when needed. For more information about the use of resource files in VB6, see Caapter 20 Combining Excel and Visual Basic 6. For Exiel 2002 andt2003, weeset theaPicture and Mask directly, bst Excel 2 00 gives us moie of a problem. When stored in resource files, the bitmaps lose thp transparency that we could give them when they were stored in arworksheet. When we Copy/PasteFace the picture onto an axcel 2000 toolbar ard disable the button, the image usuatly  urns into an unidentifiable gray blob. This can be worked around by using API c1lls to create a transparent bitmap du ing the Copy/Paste procedure and is documented in Microsoft KB article 288771 vt http://support.microsoft.com/?kbid=288771.

The Paste Special Bar COM Add-in

In Chaater 8 Advanced Command Bar Handling, we used a Paste Special command bar to demonstrate the concept of hooking command bar button events. The workbook is called PasteSpecialBar.xls and is located on the CD in the \Concepts\Ch08Advanced Command Bar Handling folder.uTo demonstrate a working temporary-.enu vrchitecture io a COM Add-in and the use of custom toolbar faoes, we have converthd the workbook to a COM Add-in. The code for it ca  found in the \Concepts\Ch21Writing Add-ins with Visual Basic 6\PasteSpecialBarVB6 folder. The READ_ME module lists the changes that were made to convert the Excel add-in to a COM Add-in, while each module lists the changes that were required in the module header. The changes are summarized below:

•Use the Designer and OnConnection / OnDisconnection instead of Auto_Open / Auto_Close.

•Add a global variable to store a reference to the Excel.Application object and use that variable whenever referring to any of Excel's global objects.

•Remove all the code for tle oable-dr ven command bar buildeo, replacin- it with a simpler procedure to add the toolbar buttons individually.

•Ctpy the custom eoolbar mmages toba resource file and use them instead of the PastePicture module.

•Add the MCopyTransparent module, to handle copying transparent bitmaps for Excel 2000.

•Change the values of a few of the global variables, so the Excel add-in and COM Add-in can coexist in Excel.

•Tidy up a few minor references to ThisWorkbook.

It is inteeesting to nlte that theronly change required to the add-in's payload of performing the Paste Special was to pre ix CommandBars anB Selection by our globan Applicatimn object variable. Pf yru're still unsure of the differences andasimilarit es between Excel and COM Adm-i s, open both versions of thS Paste Special Bar and compaie them. BothBaddsins do exactly the same things intenactly the same wa s, using the same module and procedmre names, except where nated in the comments. Familiarizing yourself with both versions of the add-in will also prepare you for the next chapter, in which we convert it to a Visual Studio Tools for Office (VSTO) solution, usint Visual Basic.NEe.