Example 2To add a group in the Extra tab that should only be visible when a particular sheet is active can be done in the following way.Start a new document in Notepad, insert the following text and save it to the desktop as ExtraTab.xml: <customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon startFromScratch="false"> <tabs> <tab id="customTab" label="Extra"> <group id="firstGroup" label="First Group"> <button id="button1" label="First button" onAction="Youhitme1"/> <button id="button2" label="Second button" onAction="Youhitme2"/> <menu id="menu1" label="Menu example"> <button id="menuButton1" label="First menu item" onAction="MenuChoice"/> <button id="menuButton2" label="Second menu item" onAction="MenuChoice"/> </menu> </group> <group id="onlySheet2" getVisible="GetVisible" label="Only Sheet2"> <button id="buttonA" label="Button A" onAction="Sheet2.ButtonA"/> <button id="buttonB" label="Button B" onAction="Sheet2.ButtonB"/> </group> </tab></tabs></ribbon></customUI>We added a group Only Sheet2 which obviously must only be visible when Sheet2 is active. In red are event properties. The first instructs Excel to run the onLoad procedure. We take the opportunity to determine there whether the group must be visible or not. The GetVisible procedure is called every time the Ribbon is redrawn. Create a new workbook with at least 2 sheets and enter the following code in Module1: Option Explicit Public isVisible As Boolean, theRibbon As IRibbonUI Sub OnLoad(ribbon As IRibbonUI) Set theRibbon = ribbon If ActiveSheet Is Sheet2 Then isVisible = True End Sub Sub GetVisible(control As IRibbonControl, ByRef visible As Variant) visible = isVisible End Sub Sub YouHitMe1(control As IRibbonControl) MsgBox "Hello, you clicked 'First button'" End Sub Sub YouHitMe2(control As IRibbonControl) MsgBox "Hello, you clicked 'Second button'" End Sub Sub MenuChoice(control As IRibbonControl) Select Case control.ID Case "menuButton1" MsgBox "Hello, you clicked 'First menu item'" Case "menuButton2" MsgBox "Hello, you clicked 'Second menu item'" End Select End SubNow we need a procedure for each button in the Only Sheet2 group. We put them in the Sheet2 VB code window together with two event procedures to show the group when Sheet2 is a activated or hide it on deactivation: Option Explicit Sub ButtonA(control As IRibbonControl) MsgBox "You clicked 'Button A'" End Sub Sub ButtonB(control As IRibbonControl) MsgBox "You clicked 'Button B'" End Sub Private Sub Worksheet_Activate() isVisible = True theRibbon.InvalidateControl "onlySheet2" End Sub Private Sub Worksheet_Deactivate() isVisible = False theRibbon.InvalidateControl "onlySheet2" End SubThe InvalidateControl method redraws the Ribbon. Save the workbook to the desktop as Test2.xlsm and close it. Replace the .rels file and copy the ExtraTab.xml file as described in example 1. Start Test2.xlsm. The first group in the Extra tab will be visible in all sheets but the second group will appear only when Sheet2 is active and will be hidden when any other sheet is active.
| |