Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)

Posted on by Anonymous

Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)

In addition to Tushar Mehta’s contributions about RibbonX and Excel I thought it would be suitable to add some knowledge from a COM Add-in perspective. However, the issues he address are also applicable to creating COM Add-ins with classic VB therefore this post is more about ‘how to’ then exploring it further. But in an upcoming post I will review some aspects when I discuss Ribbon and ‘managed’ COM Add-ins.

The Customized RibbonX ‘Manifest’

The more I work with the .NET platform and with Excel 2007 the more I appreciate the ability to create and read ‘manifests’. Actually, it reminds me of the ini files we once used on the Windows 3.o/3.1 – platform. Together with resource files it offers flexibility that also cover situations where international usefulness is important.

In addition, XML files is used with ‘managed’ COM Add-ins (developed with VB.NET 2005 and with the coming VSTO 3.0). Per se the information does not need to reside in a file, i e it can also be part of the code.

Below is the content of the XML file which also shows the ‘structure’ I prefer to work with:

<customui xmlns=“http://schemas.microsoft.com/office/2006/01/customui”>
  <ribbon startFromScratch=“false”>
    <tabs>
      <tab id=“Notes.Tab” label=“Notes Tool” visible=“1”>
        <group id=“Notes.Group” label=“Notes Toolset” visible=“1”>
          <button id=“wbk”
                  label=“Send Workbooks”
                  screentip=“Send Workbooks with Lotus Notes.”
                  getImage=“GetImage”                    
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“wst”
                  label=“Send Worksheets”
                  screentip=“Send Worksheets with Lotus Notes.”
                  getImage=“GetImage”
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“hlp”
                  label=“Help”
                  screentip=“Help with Notes Tool.”
                  getImage=“GetImage”  
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“abt”
                  label=“About”
                  screentip=“About Notes Tool.”
                  getImage=“GetImage”  
                  size=“large”
                  onAction=“NotesButton” />
          <button id=“xld”
                  label=“XL-Dennis”
                  screentip=“Visit XL-Dennis English Page.”
                  getImage=“GetImage”  
                  size=“large”
                  onAction=“NotesButton” />
        </group>
      </tab>
    </tabs>
  </ribbon>
</customui>

The Code in the Connection class

Below is the code for the connection class:

Option Explicit
‘Note: No error handling is implemented in the example.
‘Only the usual references are used for the project, i e Office 12 and Excel 12.
‘In order to customize the Ribbon UI the IRibbonExtensibility must be implemented.
Implements IRibbonExtensibility

Private Sub AddinInstance_OnConnection(ByVal Application As Object, _
    ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, ByVal AddInInst As Object, custom() As Variant)
‘The variable xlApp is declared in a standard module.
Set xlApp = Application
End Sub

Private Sub AddinInstance_OnDisconnection _
    (ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
‘Release the object from memory.
Set xlApp = Nothing
End Sub

Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
‘Parse the XML to the Ribbon.
IRibbonExtensibility_GetCustomUI = GetRibbonXML(App.Path & “NotesRibbon.xml”)
End Function

Public Function GetRibbonXML(ByVal stXMLFile As String) As String
‘Retrieve the content of the XML file.
Dim inFile As Integer
Dim stRibbonXML As String
inFile = FreeFile()
‘Read the XML file in a chunk.
Open stXMLFile For Binary As #inFile
stRibbonXML = Space(LOF(inFile))
Get #inFile, , stRibbonXML
Close #inFile
GetRibbonXML = stRibbonXML
End Function

‘Callbacks function for the customized Notes Tool.
Public Function NotesButton(ByVal control As Office.IRibbonControl)
Select Case control.Id
    Case “wbk”: Show_Form (“wbk”)
    Case “wst”: Show_Form (“wst”)
    Case “hlp”: Show_Help
    Case “abt”: Show_Form (“abt”)
    Case “xld”: Visit_XLDennis
End Select
End Function

‘Callbacks function for customized images.
Public Function GetImage(ByVal control As Office.IRibbonControl) As IPictureDisp
‘The bmp files are added to a resource file.
Select Case control.Id
    Case “wbk”: Set GetImage = LoadResPicture(101, vbResBitmap)
    Case “wst”: Set GetImage = LoadResPicture(102, vbResBitmap)
    Case “hlp”: Set GetImage = LoadResPicture(103, vbResBitmap)
    Case “abt”: Set GetImage = LoadResPicture(104, vbResBitmap)
    Case “xld”: Set GetImage = LoadResPicture(105, vbResBitmap)
End Select
End Function

The output

The following screen shot shows the outcome of the above approach:

Customized Ribbon

Questions

In the following image the screen tip is shown for the first button:

F1 Help

Does anyone know how we can:
1. hook up the F1 button to a customized help when ‘pressing the F1-button for more help’?
2. hide it / remove it if there are no customized help available?

For a deeper discussion about RibbonX please see Jensen Harris blog.

Finally, I would like to thank John West for his recent post about RibbonX, Excel and classic VB at xtremeVBtalk.

In my next post on the subject I will cover how to do it with VB.NET 2005 and for ‘managed’ COM Add-ins.

Kind regards,
Dennis

FYI, I will not be able to monitor DDOE for the coming week but don’t let that prevent anyone from making comments ;)

Posted in Uncategorized

6 thoughts on “Customize RibbonX UI for Excel 2007 with COM Add-ins (classic VB)”

  1. Hi Dennis, nice article. :-)

    I actually like how you make your XML actually readable, it’s a nice style.

    When you ask here:

    “Does anyone know how we can:
    1. hook up the F1 button to a customized help when ‘pressing the F1-button for more help’?
    2. hide it / remove it if there are no customized help available?”

    I think that as of now it either cannot be done or Microsoft has not yet exposed this aspect to Ribbon extensibility (or has not yet told us how). Right now, hitting F1 goes straight to generic Office Help, but it’s pretty obvious that this is intended to allow developers to link F1 within the supertip to a specific help file (at some point, anyway). I would think that a ‘HelpFileID’ or the like would be set as an attribute for the control… We’ll have to wait and see, for now I’ve not seen this discussed anywhere (and a quick Google search suggests the same).

    I know you’re away for the week… I hope all goes well for you!
    Mike

  3. Hi Dennis

    Really nice article!

    Unfortunately, I can’t create buttons with my own picture – as you discribe. I’m asking me, if the pictures has to match some specifications (color depth and resolution).

    Have you any experiance with several kind of images (jpeg, bmp, ico)? Or could you make your project available for donload (within your ressource file)?

    Thanks in advance!

    Regards
    Rocky – from Switzerland

  4. Have you been able to figure out:

    2. hide it / remove it if there are no customized help available?”

    Thanks
    Alex

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.