Synchronize Ribbon Dropdown to Display Active Worksheet

Question

I have a custom Excel Ribbon containing a dropdown that allows the user to switch worksheets. This works fine, however, it only works one way. For example, one scenario is the user selects a sheet name in the dropdown and the sheet changes to that one. But if they change the sheet tab, the dropdown does not update. What I need is the dropdown to always display the active sheet, no matter how the user changes it.

How can I do this?

XML

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon>
        <tabs>
            <tab id="tab1" label="Custom Tab" insertAfterMso="TabHome"> 
                 
        <!--Dropdown-->
        
                <group id="Group5" label="Dropdowns">
                    
        <dropDown id="dropDown1" label="Dropdown Box" onAction="DDonAction">
        <item id="item1" label="Sheet1" imageMso = "HappyFace" />
        <item id="item2" label="Sheet2" imageMso = "FontColorMoreColorsDialog" />
        <item id="item3" label="Sheet3" imageMso = "NewDistributionList" />
        </dropDown>

                </group>         
                   
            </tab>
        </tabs>
    </ribbon>
</customUI>

VBA

'Dropdowns

Sub DDonAction(control As IRibbonControl, id As String, Index As Integer)

        Select Case Index
        
        Case 0
            ActiveWorkbook.Sheets("Sheet1").Activate
        Case 1
            ActiveWorkbook.Sheets("Sheet2").Activate
        Case 2
            ActiveWorkbook.Sheets("Sheet3").Activate
    End Select

    End Sub

I think this Microsoft page might help you. https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/700e4451-8706-40c5-8d7b-896e4ae21b69?redirectedfrom=MSDN — xShen, Dec 29 '20 at 19:14
I misunderstood your question the first time I read it. You need to capture the `WorkSheet_Activate` event on each sheet in order to have code run when the worksheet is changed. https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.activate(even) — HackSlash, Dec 29 '20 at 23:41
Finding the ribbon and changing the selection of an element is more difficult. It's easier if you created the ribbon from VBA and already have an object handle on it. If you don't, then you would need to get a handle on the ribbon before you can modify it's children. You can see a discussion of this here: https://stackoverflow.com/questions/27154573/powerpoint-add-in-loss-of-ribbonui — HackSlash, Dec 29 '20 at 23:47
It would be much easier to grab the `ActiveSheet` later when you need it. That always points to the current WorkSheet so you don't need to hold that data in the ribbon. I'm not sure why you would use a ribbon to change the worksheet when the tabs already exist. It feels like you're making this harder on yourself. — HackSlash, Dec 29 '20 at 23:54

score 0 · Answer 1 · answered Dec 30 '20 at 14:48

You need to use a callback procedure to change the control. First, in a standard module create a global variable to hold the ribbon. You'll need this so you can force the ribbon to reset itself (which is how it calls the callback)

Public goRibbon As IRibbonUI

In your XML, add an onLoad callback. This will set the global variable to the ribbon.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad = "CustomTabRibbonLoad">

Also in your XML, add a getSelectedItemID callback to your dropdown. I called it cb_dd1_getSelectedItemID, but you can name it whatever you want.

<dropDown id="dropDown1" label="Dropdown Box" onAction="DDonAction" getSelectedItemID="cb_dd1_getSelectedItemID">

Save the XML and open the workbook. You might get some errors because you told the ribbon to look for callback procedures that don't exist. Add the callback procedures to a standard module.

Sub CustomTabRibbonLoad(ribbon As IRibbonUI)
    Set goRibbon = ribbon
End Sub

Sub cb_dd1_GetSelectedItemID(control As IRibbonControl, ByRef itemID As Variant)
    itemID = "item" & ActiveSheet.Index
End Sub

The first one simply sets your global variable to the ribbon. The second one gets called whenever the ribbon is invalidated. It sets the selected item in the dropdown. Since your IDs are item1, item2, and item3, I concatenated "item" to the active sheet's index.

In the ThisWorkbook module, add a Sheet_Activate event.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    goRibbon.Invalidate
    
End Sub

Every time the user activates a sheet, the ribbon gets invalidated, the getSelectedItemID callbacks fires, and the dropdown is updated with the active sheet's name.

Synchronize Ribbon Dropdown to Display Active Worksheet

XML

VBA

1 Answers1