CODEWITHSUNDEEP
excelvbatabsribbon
Thomas Sz.
Thomas Sz.

Reputation: 11

How to trigger a macro with change of ribbon tab?

I have an Excel Workbook with a couple of custom ribbon tabs. I would like to activate a specified Sheet when clicking of particular custom ribbon tab.

Details: The Workbook composed of a 5 Sheets has following additional ribbon tabs: "Parameters" and "Data Analysis". Each Tab has a few groups of controls. I need to add a trigger (macro?), which would automatically change a Sheet to Sheet3 (to run ActiveWorkbook.Sheets("Sheet3").Activate) only when a user picks the "Data Analysis" Tab.

I would appreciate any help.

Upvotes: 1

Views: 862

Answers (1)

Victor K
Victor K

Reputation: 1049

It is possible to use a getVisible callback from a ribbon to achieve what you are looking for.
Here is an example xml for a tab (it should be nested inCustomUI and ribbon tags) 

<tabs>
    <tab id="ExampleTab" label="Example Tab">
        <group id="ExampleGroup" 
            label="Example Group"
            getVisible="GetVisibility"
            tag="1"
            >
        </group>
    </tab>
</tabs>

When someone clicks on the tab this xml will call GetVisibility sub in the workbook it belongs to. You can use tag property to pass a variable to the `Sub'. For example every tab can have its own tag, or it can be used for something else.

Now here is an example code for GetVisibility: 

Public Sub GetVisibility(Control As IRibbonControl, ByRef Visible)
    'your code to activate the worksheet can go here
     Visible = True 'if you want to make a control visible
     Select Case Control.Tag 'you can use Control.Tag to get a variable from ribbon
         Case "1"
         Case Else
     End Select
End Sub

The only issue is that this code will normally only be called the first time you click on a tab. To get the code to rerun I think you need to invalidate your ribbon control. For that you would have to store your ribbon in a global scope variable at initialization. The last time I checked you may need a workaround for that, such as storing ribbon address in a worksheet. There is a number of question on Stack about that, for example: this one.

Upvotes: 1

Related Questions