excelvbaexcel-2007ribbonxfluent-ribbon

Invalidate doesn't hide tabs


I am trying to push the limits of my understanding of the Fluent Ribbon/RibbonUI system in the Office 2007/2010 framework.

Coupling that with my knowledge and understanding of XAML design for WPF/Silverlight, i wanted to see if i could dynamically show/hide the described Office Menu items below and it seems i have FUBAR'd something up.

Please advise where i went wrong, or where i need to further develop:

CustomUI.xml

<group id="grpITOfficeMenu"
            label="Office Menu">
    <button id="btnShowOffice"
                label="Show"
                onAction="ShowOfficeTabs"/>
    <button id="btnHideOffice"
                label="Hide"
                onAction="HideOfficeTabs" />
</group>
<group id="grpITContextualTabs"
            label="Contextual Tabs" >
    <button id="btnShowContext" 
                label="Show" 
                onAction="ShowContextualTabs"/>
    <button id="btnHideContext"
                label="Hide"
                onAction="HideContextualTabs"/>
</group>
...
<officeMenu>
    <button idMso="FileNew"
                getVisible="OfficeGetVisible" />
    <button idMso="FileOpen"
                getVisible="OfficeGetVisible" />
</officeMenu>
<contextualTabs>
    <tabSet idMso="TabSetSmartArtTools"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetChartTools"
                getVisible="ContextualGetVisible"/>
    <tabSet idMso="TabSetDrawingTools"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetPictureTools"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetPivotTableTools"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetHeaderAndFooterTools"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetTableToolsExcel"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetPivotChartTools"
                getVisible="ContextualGetVisible" />
    <tabSet idMso="TabSetInkTools"
                getVisible="ContextualGetVisible" />
</contextualTabs>

VBA:

'Method to Refresh the RibbonUI object
Sub RefreshRibbon(tag As String)
   'Check if Ribbon variable has been initialized with Ribbon Object from Excel
   If Not (Rib Is Nothing) Then
      'Ribbon variable has been initialized.
      MyTag = tag
      Rib.Invalidate
   End If
End Sub
'Flip OfficeMenu Tabs visible based on @OffVisible value
Sub OfficeGetVisible(control As IRibbonControl, ByRef returnVal)
   returnVal = OffVisible
End Sub
'Flip Contextual Tabs visible based on @ContVisible value
Sub ContextualGetVisible(control As IRibbonControl, ByRef returnVal)
   returnVal = ContVisible
End Sub
...
'Show/Hide ContextualTabs in the IT Mode
Sub ShowContextualTabs(Optional ctrl As Variant)
   ContVisible = True
   RefreshRibbon tag:=MyTag
End Sub
Sub HideContextualTabs(Optional ctrl As Variant)
   ContVisible = False
   RefreshRibbon tag:=MyTag
End Sub
'Show/Hide OfficeMenu Tabs in the IT Mode
Sub ShowOfficeTabs(Optional ctrl As Variant)
   OffVisible = True
   RefreshRibbon tag:=MyTag
End Sub
Sub HideOfficeTabs(Optional ctrl As Variant)
   OffVisible = False
   RefreshRibbon tag:=MyTag
End Sub

Update:

Did some detailed testing and it technically works, but what I am trying to ultimately have completed is the ability to show/hide Home, Insert, Page Layout, Formulas, Data, Review, View, Developer, etc. tabs from the ribbon. I'm trying to remove the need, or necessity, of the startfromscratch attribute in the customui element.


Solution

  • Got it solved, had to go look at ALL of the idMso values for both TabSets & Tab objects in the Ribbon.

    So here is the XML for the CustomUI to allow for Show/Hide of some of the Default (at least the ones i needed) Tab/TabSet's. Using the same Show/Hide methods above.

    <customUI onLoad="s_UIOnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
      <ribbon>
        <!-- Excel File Menu options, not available through Tab or TabSets -->
        <officeMenu>
          <button idMso="FileNew" getVisible="OfficeGetVisible" />
          <button idMso="FileOpen" getVisible="OfficeGetVisible" />
        </officeMenu>
        <!-- Excel TabSets that only become visible when certain objects
        are selected (such as ListObjects, PivotTables, Charts, etc) -->
        <contextualTabs>
          <tabSet idMso="TabSetChartTools" getVisible="ContextualGetVisible"/>
          <tabSet idMso="TabSetDrawingTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetEquationTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetHeaderAndFooterTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetInkTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetPictureTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetPivotChartTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetPivotTableTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetSlicerTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetSmartArtTools" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetSparkline" getVisible="ContextualGetVisible" />
          <tabSet idMso="TabSetTableToolsExcel" getVisible="ContextualGetVisible" />
        </contextualTabs>
        <tabs>
          <!-- Excel Menu Tabs, these are not included in the TabSets and
        must be explicitely defined -->
          <tab idMso="TabAddIns" getVisible="OfficeGetVisible" />
          <tab idMso="TabBackgroundRemoval" getVisible="OfficeGetVisible" />
          <tab idMso="TabData" getVisible="OfficeGetVisible" />
          <tab idMso="TabDeveloper" getVisible="OfficeGetVisible" />
          <tab idMso="TabFormulas" getVisible="OfficeGetVisible" />
          <tab idMso="TabHome" getVisible="OfficeGetVisible" />
          <tab idMso="TabInsert" getVisible="OfficeGetVisible" />
          <tab idMso="TabPageLayoutExcel" getVisible="OfficeGetVisible" />
          <tab idMso="TabReview" getVisible="OfficeGetVisible" />
          <tab idMso="TabView" getVisible="OfficeGetVisible" />
          ...
        </tabs>
      </ribbon>
    </customUI>