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
1 Downloads
-
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: Sheet1
, Sheet2
, Sheet3
, wstRibbonX
. The wstRibbonX
worksheet should be very hidden and have a hidden named range called rRibbonXPointer.
3a XML
<?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 >
3b VBA
- 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.ProtectionMode
to 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 UserInterfaceOnly
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 Worksheet.ProtectionMode
property.
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 Open()
, BeforeClose()
and 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.
Pingback: Default Values With RibbonX Dropdowns | RAD Excel
Pingback: A Guided Tour Through The VBA IDE’s Options | RAD Excel