Reputation: 11
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
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