A Custom Tab RibbonX Template Which Handles State Loss

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:

• losing the state of the global IRibbonUI ribbon object
• Change the Ribbon in Excel 2007-2013
• Hide or Display Ribbon Tab/Group/Control with getVisible

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.

Advertisements