Dynamic Ribbon Control & Custom UI Resources

[Jeffrey Smith]

Over the past couple of weeks I took it upon myself to learn how to create a custom ribbonX menu. I didn't find much information about the ribbon extensibilty while searching through the MrExcel message board. I found information to help me out from many different sites.

I have created many traditional commandbar menus in version 2003 through 2007. I still have a long way to go to learn how to interact with the controls on the ribbon, but I finally think I have the basics down.

I want to share the code and resources with all and I hope others could post to this with their resources that helped them.

The following code is an example of a custom UI ribbonX for excel. It shows a new tab, two groups and a few controls. The main focus is a dropdown box that gets dynamically loaded with the names of visible sheets in the workbook. When a user selects one of the names in the dropdown, it activates that sheet. I added VBA code to have the dropdown change the name shown when a user selects a visible sheet using the tabs.

I used two tools to complete the task: The CustomUIEditor from microsoft which I downloaded from here. Do not try to edit a customUI using CustomUIEditor while you have the workbook loaded into excel; if you do, you may lose your work. This is a simple tool for viewing the xml files and creating the callback routines needed by excel. Microsoft needs to put more work into this. I would rather see an editor within the Vsiual Basic editor in Excel.

Andy Pope's RibbonX Visual Designer which can be found here. This is a good tool that is run inside of Excel. It has a preview tool and it shows the built-in icons from MS office. You need to have the workbook unloaded before opening your project.

I just learned over the last week that MS Office 2007 files are nothing more than compressed files containing xml sheets. You can rename a *.xlsm file to a *.zip file and look inside. When you add a custom ribbon to an Excel workbook, it creates a folder in the compressed file called "customUI" and saves a file in that folder called "customUI.xml". My current project is about 40mb large as a .xlsm file. When I unzip it, it becomes over 134mb large. Now I understand why Excel2007 takes so long to load workbooks!



For some reason there are problems showing the xml code in the code window. I will try attach the xml code soon.


Here is the code only related to the dropdown control. Put this into a standard module.

Option Explicit
'
Public TSNavItems As Integer      'Number of visible sheets (index zero)
Public TSNavArray() As String     'Store names of visible sheets
Public TSNavSelected As String    'Store selected sheet from control
Public TSRibbon As IRibbonUI      'Time Series Custom Ribbon
Public TSWorkbook As Workbook     'Time Series Workbook
Public TrackTSNav As Integer      'Store the current activated sheet index
'
'This counts and loads the visible sheets and chart sheets into the navigation dropdown
Private Sub LoadVisibleNavigate()
'
  Dim shtCount As Long, wksSheet As Worksheet, ChtSheet As Chart
  
  TrackTSNav = 0
  
  'Count all visible worksheets
  shtCount = 0
  For Each wksSheet In TSWorkbook.Worksheets
    If wksSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
    End If
  Next wksSheet
  'Add the visible Chart Sheets
  For Each ChtSheet In TSWorkbook.Charts
    If ChtSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
    End If
  Next ChtSheet
  
  
  'Load all visible worksheets into the Navigation Dropdown array for ribbon
  TSNavItems = shtCount             'index one
  ReDim TSNavArray(TSNavItems - 1)  'index zero
  shtCount = -1
  For Each wksSheet In TSWorkbook.Worksheets
    If wksSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
      TSNavArray(shtCount) = wksSheet.Name
      
      'Set the default index for the dropdown to the current activated sheet
      If wksSheet.Name = ActiveSheet.Name Then TrackTSNav = shtCount
    End If
  Next wksSheet
  'Load the visible Chart Sheets in the dropdown array
  For Each ChtSheet In TSWorkbook.Charts
    If ChtSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
      TSNavArray(shtCount) = ChtSheet.Name
      
      'Set the default index for the dropdown to the current activated sheet
      If ChtSheet.Name = ActiveSheet.Name Then TrackTSNav = shtCount
    End If
  Next ChtSheet
  
  
'
End Sub
'
'This gets called when the workbook loads
Private Sub myRibbon_******(ribbon As IRibbonUI)
   Set TSRibbon = ribbon          'My ribbon
   Call LoadVisibleNavigate       'Load the Navigation dropdown
End Sub
'
'This gets called for every item in the dropdown
'This callback gets called during startup and can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getItemCount(control As IRibbonControl, ByRef returnedVal)
'
  'The total number of items in the dropdown box (index one based)
  returnedVal = TSNavItems
'
End Sub
'
'This callback gets called during startup automatically.  It can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getItemID(control As IRibbonControl, index As Integer, ByRef id)
'
  'This is an internal id used by xl.  It must be unique
  id = "TSNavSheets" & index
'
End Sub
'This callback gets called during startup automatically.  It can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'
  'returns the name of the sheet
  returnedVal = TSNavArray(index)
 '
End Sub
'
'This callback gets called during startup automatically.  It can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
'
  'TrackTSNav is the index of the current selected sheet
  returnedVal = TrackTSNav
'
End Sub
'

'This callback only fires when a user changes the dropdown
Public Sub TS_Navigate_OnAction(control As IRibbonControl, id As String, index As Integer)
'
  TSWorkbook.Activate                 'My workbook
  Sheets(TSNavArray(index)).Activate  'Activate the sheet selected in the dropdown
'
End Sub

Here is the code that needs to be put into the "ThisWorkbook" module.

Private Sub Workbook_Open()
  
  xlVersion = Val(Mid(Application.Version, 1, InStr(Application.Version, ".") - 1))
  'Version 12 = Excel2007
  'Version 11 = Excel2003
  'Version 10 = Excel2000
  'Used to decide if the old commandbar menu is loaded
  
  Set TSWorkbook = ThisWorkbook   'My Workbook
End Sub
 
'
'The Navigation dropdown control changes when a user selects a new sheet
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim X As Long
  
  'Find the index value for the active sheet in the dropdown array
  For X = 0 To TSNavItems - 1
    If TSNavArray(X) = ActiveSheet.Name Then
      TrackTSNav = X
    End If
  Next X
  TSRibbon.InvalidateControl ("drp_Navigate")   'Reset the dropdown control
End Sub

 

[Jeffrey Smith]

I'm going to have to figure out how to post the xml code

 

[Jeffrey Smith]

finally!!

<customUI 
 xmlns="http://schemas.microsoft.com/office/2006/01/customui"
 ******="myRibbon_******">
 <ribbon >
  <tabs >
   <tab 
    id="TSTab"
    label="Time Series"
    tag="TS">
    <group 
     id="grpNavigate"
     imageMso="UpgradeWorkbook"
     label="Navigate"
     tag="TS">
     <dropDown 
      id="drp_Navigate"
      keytip="&TN"
      label="Navigate to:"
      screentip="Navigate to other sheets"
      sizeString="WWWWWWWWWWW"
      tag="TS"
      getItemCount="drp_Navigate_getItemCount"
      getItemID="drp_Navigate_getItemID"
      getItemLabel="drp_Navigate_getItemLabel"
      getSelectedItemIndex="drp_Navigate_getSelectedItemIndex"
      onAction="TS_Navigate_OnAction"/>
    </group >
    <group 
     id="grpOptions"
     imageMso="PageBreakInsertOrRemove"
     label="Split Screen Options"
     tag="TS">
     <comboBox 
      id="cmbo_OptionList"
      imageMso="DatasheetView"
      label="Option List"
      screentip="Choose an Option to Show"
      sizeString="Option 99"
      tag="TS"/>
     <button 
      id="btn_OptionsOff"
      imageMso="ErrorChecking"
      label="Turn off Options"
      screentip="Turn off Time Series Options"
      tag="TS"
      onAction="btn_OptionsOff_onAction"/>
    </group >
   </tab >
  </tabs >
 </ribbon >
</customUI >

 

[Jeffrey Smith]

For some reason the hyperlinks didn't work. I will try again.

CustomUIEditor

RibbonX Visual Designer

 

[LoquaciousDandy]

<customui xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******"><customui xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******"><customui xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******"><customui xmlns="http://schemas.microsoft.com/office/2006/01/customui" ******="myRibbon_******">I thought there was an error in your code, but in trying to post the correction, I've discovered something interesting about this forum: it censors the "on Load" code, so in all the places above where there are a collection of asterices (******), you need to replace it with "on Load" (without spaces or inverted commas). There should be 3 replacements; one in the Module code, and two in the XML.

With that fixed, it works a charm. Cheers!</customui></customui></customui></customui>

 

[Jeffrey Smith]

Thank you for pointing that out.

 

[HarryPrice]

Hi Jeffrey,

Apologies this is so long after your original post!

I've been using the drop-down code for some time now, and it's working really well. I'm currently working on a new workbook that has the worksheet drop-down in the Ribbon, and also a toggle button which alters the visibility of some of the sheets.

I'm having problems figuring out what callbacks are required after some of the sheets have been hidden, to reflect the changes in the drop-down box.

Any help would be massively appreciated.

Thanks,

Harry