Willy's
Freeware

 Windows 95...XP
 Agenda 
 Belgian postal codes 
 Calculator 
 Htmlpad 
 Links2Tray 
 Startup manager 
 Excel 97...2007
 Alphabetical Disorder 
 Customize the Ribbon 
 Perpetual calendar 
 DOS
 Compaction manager 
 GPS
 MTB tracks 
 Fietsroutetracks 
 Stoomtramroute 
 Fietsknooppunten 
 Other
 Download 
 How this site is setup 
 Index 
 Home 

Top

Simplicity is
the characteristic
of the real thing.

Customizing the Office 2007 Ribbon

 Introduction  Example 1  Example 2  Example 3  A real  life example 

Example 2

To 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 Sub
Now 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 Sub
The 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.

 

uptime Statistieken