The first few times you want to customise the Excel ribbon you’ll find yourself scouring the internet and cobbling together bits and pieces from different sites to get the result you want. For this post I’ve created a template in Excel 2013 which will:
Add a custom tab next to the home tab on the ribbon
Display different buttons depending on the active sheet
Handle state loss
The custom tab RibbonX template I made.
To make it easier to edit the XML so you can add whatever ribbon controls you need, the Custom UI Editor.
2 Key References
Ron De Bruin’s site really is a wonderful reference point for RibbonX work. For the current topic I would like to particularly credit these articles:
The code in my template is combined from his examples, but implemented in a slightly different way.
3 The Code
If you would like to build the template yourself rather than downloading it, here is all of the code. You’ll need a new workbook containing worksheets with the following codenames:
wstRibbonX worksheet should be very hidden and have a hidden named range called rRibbonXPointer.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="wstRibbonX.RibbonOnLoad"> <ribbon > <tabs > <tab id="tabRADExcel" insertBeforeMso="TabInsert" label="RADExcel" tag="RADExcel" keytip="CSZ" getVisible="wstRibbonX.GetVisible"> <group id="grpRADExcelSheet1" label="RAD Excel Toolkit Sheet1" tag="grpSheet1" getVisible="wstRibbonX.GetVisible"> <button id="cmdForSheet1" imageMso="AccessRefreshAllLists" label="Special Button For Sheet 1" size="normal" onAction="wstRibbonX.cmdForSheet1_Click" screentip="Only available for sheet1" tag="cmdForSheet1" /> </group> <group id="grpRADExcelSheet2" label="RAD Excel Toolkit Sheet2" tag="grpSheet2" getVisible="wstRibbonX.GetVisible"> <button id="cmdForSheet2" imageMso="FieldChooser" label="Special Button For Sheet 2" size="normal" onAction="wstRibbonX.cmdForSheet2_Click" screentip="Only available for sheet2" tag="cmdForSheet2" /> </group> </tab > </tabs > </ribbon > </customUI >
- ThisWorkbook Workbook Class Module
Option Explicit Private Sub Workbook_Open() 'workbook_open() fires before wstRibbonX.RibbonOnLoad() wstRibbonX.Initialise End Sub Private Sub Workbook_SheetActivate(ByVal Sh As Object) If Sh Is Sheet1 Then wstRibbonX.RefreshRibbon strTag:="grpSheet1" ElseIf Sh Is Sheet2 Then wstRibbonX.RefreshRibbon strTag:="grpSheet2" Else wstRibbonX.RefreshRibbon strTag:=vbNullString End If End Sub
- wstRibbonX Worksheet Class Module
Option Explicit 'credits: 'http://www.rondebruin.nl/win/s2/win015.htm 'http://www.rondebruin.nl/win/s2/win001.htm 'http://www.rondebruin.nl/win/s2/win012.htm '64-bit compatability #If VBA7 Then Private Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _ ByRef Destination As Any, _ ByRef Source As Any, _ ByVal Length As Long) #Else Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _ ByRef Destination As Any, _ ByRef Source As Any, _ ByVal Length As Long) #End If Private Const mstrRNG_RIBBONX_POINTER As String = "rRibbonXPointer" Private Const mstrRAD_EXCEL_TAB As String = "RADExcel" Private muiRibbon As IRibbonUI Private mstrTag As String 'this method will get a reference to the ribbon if it has been lost 'due to a state loss event Private Function GetRibbon() As Object '64-bit compatability #If VBA7 Then Dim lngRibPtr As LongPtr #Else Dim lngRibPtr As Long #End If Dim objRibbon As Object 'userinterfaceonly does not persist if the workbook is closed and reopened 'so if protectionmode is true then we are happy that the pointer is valid 'and from the current session If ProtectionMode Then #If VBA7 Then lngRibPtr = CLngPtr(Range(mstrRNG_RIBBONX_POINTER).Value2) #Else lngRibPtr = CLng(Range(mstrRNG_RIBBONX_POINTER).Value2) #End If 'this check is an obvious one to have, but it should be redundant 'if the protectionmode check above works as it is intended to If lngRibPtr <> 0 Then 'NB: CopyMemory will crash Excel if lngRibPtr is invalid CopyMemory objRibbon, lngRibPtr, LenB(lngRibPtr) Set GetRibbon = objRibbon End If End If End Function Public Sub Initialise() 'By default the Tabs with GetVisible in the 'RibbonX are not Visible when the workbook is opened mstrTag = mstrRAD_EXCEL_TAB End Sub Public Sub RefreshRibbon(ByRef strTag As String) mstrTag = strTag If muiRibbon Is Nothing Then Set muiRibbon = GetRibbon If Not muiRibbon Is Nothing Then muiRibbon.Invalidate End Sub 'Callback for customUI.onLoad Public Sub RibbonOnLoad(ByVal objRibbon As IRibbonUI) Const strPASSWORD As String = "RADExcelSecret" Dim objActiveSheet As Object #If VBA7 Then Dim lngRibPtr As LongPtr #Else Dim lngRibPtr As Long #End If Set muiRibbon = objRibbon lngRibPtr = VBA.ObjPtr(objRibbon) 'wstRibbonX is very hidden, but password protection gives us added 'peace of mind that the cell holding the pointer to the ribbon can't 'be accidentally changed 'userinterfaceonly only persists while the workbook is open Protect Password:=strPASSWORD, UserInterfaceOnly:=True Range(mstrRNG_RIBBONX_POINTER).Value2 = lngRibPtr 'force ribbon to show correct buttons for the activesheet Set objActiveSheet = ThisWorkbook.ActiveSheet If Not objActiveSheet Is Nothing Then Activate objActiveSheet.Activate End If End Sub 'Callback for getVisible Public Sub GetVisible(ByVal ctlRibbon As IRibbonControl, ByRef returnVisible As Variant) returnVisible = (ctlRibbon.Tag = mstrRAD_EXCEL_TAB) Or (ctlRibbon.Tag = mstrTag) End Sub 'Callback for cmdForSheet1 onAction Public Sub cmdForSheet1_Click(ByVal control As IRibbonControl) MsgBox "Hello!" End Sub 'Callback for cmdForSheet2 onAction Public Sub cmdForSheet2_Click(ByVal control As IRibbonControl) MsgBox "Goodbye!" End Sub
4 Why Did I Code It Like That?
Ron’s site already explains the general logic for the code, so I’m going to assume you’re up to speed with RibbonX and would like to explain why I encapsulated the ribbon related VBA code in a worksheet class module. It seems like a strange choice at first, but it offers a number of compelling advantages which make it a reasonable approach:
- I use a cell on the sheet to hold a pointer to the ribbon
- I use
Worksheet.ProtectionModeto ensure that the pointer is current and valid.
- I use it to toggle the active sheet when the ribbon loads, forcing the ribbon to show the correct controls.
- I could use the sheet to store reference data for ribbon, such as a list for a combobox (it isn’t used in this way in this particular template)
Let’s talk about the first three points in more detail.
The code in the template revolves around the fact that we need to maintain a reference to the ribbon using a variable called
muiRibbon. It’s a module level variable which means that it holds its value until the workbook is closed or a state loss event occurs.
A state loss event causes all of the variables in the workbook to lose their values and any in-memory objects to be destroyed. State loss events can be triggered in many ways: a simple example is when a runtime error occurs and the user clicks on the End button instead of the Debug button (this is the equivalent of executing the VBA
End keyword). In case you’re interested, there’s an option in the VBA Editor > Tools > Options to notify you before state loss occurs at design-time:
We can’t stop state loss from happening so if the
muiRibbon variable loses its reference to the ribbon, the code gets the reference back in the
GetRibbon() method by using a
CopyMemory() API call. To do this, when the workbook is first opened, a pointer (which is a numeric value) to the ribbon is saved. The pointer has to be saved somewhere which isn’t affected by state loss, so the immediately available places to store it in would be either a cell, a name or in the registry. A cell is an easy choice so the template contains a very hidden worksheet with a named range called
rRibbonXPointer. The pointer is saved into the cell in the
RibbonOnLoad() callback which is called when the workbook is opened and the ribbon is loaded.
When you use the
CopyMemory() API you have to be extremely careful. If you call it with an invalid
Source then Excel will crash, which brings me to my next point: how can the code tell if the pointer to the ribbon is from the current session and not one from a previous session? After all, it’s just a number saved in a cell. To do this, I use a special characteristic of worksheets which are protected via the
Worksheet.Protect() method with the
UserInterfaceOnly parameter set to
True. When a worksheet is protected in this way, the
True behaviour (which means that code can edit the sheet but a user cannot) only persists while the workbook is open. Assuming the workbook is saved, when it is closed and re-opened the worksheet will still be protected but the
UserInterfaceOnly behaviour is set back to
False. You can tell if
UserInterfaceOnly is set to
True by checking the
Therefore, by putting the pointer in a cell and protecting the sheet with
UserInterfaceOnly set to
True each time the workbook is opened, not only do we get peace of mind from the added protection on the very hidden sheet, we can check its
ProtectionMode property to be sure that the ribbon pointer is fresh from the current session. If we stored the pointer in a name or in the registry rather than in a cell, then we could manage it using relevant combinations of the workbook’s
BeforeSave() event handlers but, in my opinion, doing it this way is more robust.
The final use of the hidden sheet is that when the
wstRibbonX.RibbonOnLoad() method is called, I can take note of the
ActiveSheet, activate the
wstRibbonX sheet and then activate the previously active sheet again: this raises two
SheetActivate events, the latter of which forces the correct controls to be shown on the ribbon for the active sheet when the workbook is opened.
So there you have it: the plumbing you need is already in place in the template, so all you need to do is edit the XML to add the controls you want and then add the controls’ callbacks into the
wstRibbonX class module. I hope it comes in handy for you and saves you some time.
Edit 8th September: I’ve made a small change to the code which removed some redundant variables and moved around the order of some of the events. I don’t have 64-bit Excel to test the code on: as mentioned earlier, the conditional compilation was taken from Ron De Bruin’s site so if you spot any problems please let me know.