Synchronize Ribbon Dropdown to Display Active Worksheet

user_10780707 0
Viewed 180

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
office365 ribbon vba excel xml
1 Answers

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.

dkusleika_42800
Related