1

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.

Thomas Sz.
  • 11
  • 1
  • Perhaps you can add code to `getVisible` callback of a child element of a ribbon tab. You may need to invalidate other tabs when activating one of them. – Victor K Apr 28 '20 at 17:35
  • Can you show us any attempts at solving this problem? – RowanC Apr 29 '20 at 06:33

1 Answers1

1

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.

Victor K
  • 1,049
  • 2
  • 10
  • 21