Adding Ribbon Controls

Chapter 14 explained how VBA applications can modify the Ribbon by creating custom tabs, groups, or controls. This was done by creating a text file containing the XML for the custom UI definition and adding it to the XML workbook file. When Excel loads the workbook, it sees the custom part and processes it, creating custom controls. As designed, this allows you to create document-level RibbonX cus-tomizations, but that chapter demonstrated how to achieve application-level customizations by simply using a standard Excel XML Add-In (.xlam). The "official" approach to application-level UI customiza-tions is to use a COM Add-In.

Obviously, COM Add-Ins don't have an XML workbook that Excel can check for any custom UI XML. Instead, each time Excel 2007 loads a COM Add-In, it checks to see if the Add-In implements another specific interface, IRibbonExtensibility. If that interface is found, Excel calls its GetCustomUI function and the Add-In returns the custom UI XML as text. After that point, the behavior of a COM Add-In is exactly the same as the VBA code shown in Chapter 14, except that all the callbacks must exist in the same class that implements the IRibbonExtensibility interface. To demonstrate this concept, this section creates a simple COM Add-In that uses RibbonX to add a menu to Excel's View tab. Start by creating a new VB6 Add-In project and performing the following steps to configure it correctly:

1. Remove the default form.

2. Delete all the code from the Designer Connect class.

3. Edit the Designer to give it a meaningful name and description, targeting Excel 12.0 and loading at startup.

4. Change the project name from MyAddIn to XLVBARibbonX.

5. Click Project O References and uncheck the reference to the Visual Basic 6.0 Extensibility library.

6. Check that the project references the Microsoft Excel 12.0 Object Library and the Microsoft Office 12.0 Object Library (which contains the definition of the IRibbonExtensibility interface), adding them if they're missing.

7. Copy the following code into the Connect class (which can also be found in the XLVBARibbonX folder in the code download for this chapter):

'Implement an interface to tell Excel we're doing things with RibbonX

Implements IRibbonExtensibility

'Store a reference to the ribbon, so we can invalidate controls when needed

Dim moRibbon As IRibbonUI

'Called by Excel at startup. Provide the custom UI.

Private Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) _ As String Dim sXML As String

'Build the XML for the custom UI

'Here, we're Adding a simple button to the middle of Excel's View tab 'Typically, this would be read from a resource file sXML = ""

sXML = sXML & "<customUI " & _

"xmlns=""http://schemas.microsoft.com/office/200 6/01/customui"" " & "onLoad=""CustomUI_OnLoad"">" <ribbon>" <tabs>"

<tab idMso=""TabView"">" <group id=""XLVBAView"" " insertAfterMso=""GroupViewShowHide"" " label=""VBA Prog Ref"">" <button id=""CTPTest"" label=""A Test"" " imageMso=""DateAndTimeInsert"" size=""large"" " onAction=""CTPTest_Click""/>" </group>" </tab>" </tabs>" </ribbon>" </customUI>"

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

sXML =

sXML

&

IRibbonExtensibility_GetCustomUI = sXML End Function

'Called by Excel to provide the Ribbon object,

'which is used to invalidate controls, forcing a refresh.

Public Sub CustomUI_OnLoad(ribbon As IRibbonUI)

Set moRibbon = ribbon End Sub

'Show a message when the button is clicked Public Sub CTPTest_Click(control As IRibbonControl)

MsgBox "Clicked me!" End Sub

Compile the DLL, start Excel 2007, click the View tab, and click the Date Picker button in the middle of the tab. You should get the Clicked me! message box.

Note that the GetCustomUI function is only called once in the life of the COM Add-In, at startup. That means you have only the one opportunity to provide your custom UI XML. Although you can change the visibility of controls, in practice that means you are extremely limited in the degree to which you can change your UI in response to changes within Excel (such as opening or closing workbooks). This is a serious deficiency of the COM Add-In RibbonX extensibility model. Instead of being asked to provide CustomUI XML at startup, Excel should provide a reference to a class factory the Add-In could use to create or modify UI customizations at any time during its life. As you'll see later in this chapter, that design is used for custom task panes and it works extremely well.

0 0

Responses

Post a comment