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:
<tab id=“Notes.Tab” label=“Notes Tool” visible=“1”>
<group id=“Notes.Group” label=“Notes Toolset” visible=“1”>
screentip=“Send Workbooks with Lotus Notes.”
screentip=“Send Worksheets with Lotus Notes.”
screentip=“Help with Notes Tool.”
screentip=“About Notes Tool.”
screentip=“Visit XL-Dennis English Page.”
The Code in the Connection class
Below is the code for the connection class:
‘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.
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
Private Sub AddinInstance_OnDisconnection _
(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, custom() As Variant)
‘Release the object from memory.
Set xlApp = Nothing
Public Function IRibbonExtensibility_GetCustomUI(ByVal RibbonID As String) As String
‘Parse the XML to the Ribbon.
IRibbonExtensibility_GetCustomUI = GetRibbonXML(App.Path & “NotesRibbon.xml”)
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
GetRibbonXML = stRibbonXML
‘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
‘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)
The following screen shot shows the outcome of the above approach:
In the following image the screen tip is shown for the first button:
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.
FYI, I will not be able to monitor DDOE for the coming week but don’t let that prevent anyone from making comments ;)