Search code examples
excelribbon-controlvba

Cannot hide custom control on excel ribbon


Been trying to hide a custom control on the excel ribbon and my code keeps showing the message from my RefreshRibbon() sub. The control remains visible no matter what string I pass from the HideImport() sub. I include here the XML for the custom ribbon and the macros that a problem.

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon startFromScratch="false">
    <tabs>
        <tab id="tabControl1" label="Control">
            <group id="grpImport1" label="Import" getVisible="GetVisible" tag="ShowFalse">
                <button id="btnDetail" label="Read Detail" image="Ex" size="large" onAction="ReadDetail" />
            </group>
            <group id="grpJourneys1" label="Journeys" getVisible="GetVisible" tag="ShowTrue">
                <button id="btnLegAdd" label="Add" image="AddLeg" size="large" onAction="AddLeg" />
                <button id="btnLegRemove" label="Remove" image="RemoveLeg" size="large" onAction="RemoveLeg" />
                <button id="btnLegRetime" label="Retime" image="RetimeLeg" size="large" onAction="RetimeLeg" />
            </group>
        </tab>
    </tabs>
</ribbon>

Dim ribControl   As IRibbonUI
Public strTag    As String

Sub RibbonOnLoad(ribbon As IRibbonUI)
   Set ribControl = ribbon
End Sub

Sub GetVisible(control As IRibbonControl, ByRef visible)
  If strTag = "Show" Then
    visible = True
  Else
    If control.Tag Like strTag Then
      visible = True
    Else
      visible = False
    End If
  End If
End Sub

Sub RefreshRibbon(Tag As String)
  strTag = Tag
  If ribControl Is Nothing Then
    MsgBox "Error, Save/Restart your workbook"
  Else
    ribControl.Invalidate
  End If
End Sub

Sub ReadDetail(control As IRibbonControl)
  MsgBox "This is Read Detail"
End Sub

Sub AddLeg(control As IRibbonControl)
  MsgBox "This is Add Leg"
End Sub

Sub RemoveLeg(control As IRibbonControl)
  MsgBox "This is Remove Leg"
End Sub

Sub RetimeLeg(control As IRibbonControl)
  MsgBox "This is Retime Leg"
End Sub

Sub HideImport()
  Call RefreshRibbon(Tag:="*True")
End Sub

Don't normally publish on here as always manage to solve it myself after reading your responses to other questions but this is driving me mad.


Solution

  • You don't have the callback function in your custom ribbon code: Example: <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="RibbonOnLoad">

    So you aren't Running the RibbonOnLoad() procedure. Therefore you are not setting the variable ribControl