Custom Ribbon disappears on sheet change

[ACommandLineKindaGuy]

I'm going to explain this qualitatively before posing code:

I have a custom Tab "Fee" that on startup is the only visible tab. All of the Excel Tabs are hidden using startFromScratch="True", however I have created custom tabs that mimic the standard Excel Tabs; these are all hidden on startup.

There are a few sheets in the workbook. For all but one sheet, I use a GetEnabled callback that disables many but not all controls.

I have a "System Access" Control that unprotects the workbook and all its sheets among other things including a callback to display all the faux Excel Tabs and keep the Fee Tab visible, but disable certain Fee tab controls.

All is good and all works great until when I am in "System Access" and change sheets, all the faux Excel Ribbon Tabs disappear. The disabling of certain controls on the Fee tab works fine.

I am calling the RefreshRibbon callback sub on each sheets Worksheet_Activate sub but without luck.

Any suggestions before I post the code?

TIA John

 

[Dan_W]

A number of things come to mind, but hard to say without any code to consider it against. Also a bit hard to visualise.

My immediate guess is that - re: these faux tabs that disappear - it sounds as though whatever you're using to display them (getEnabled? why not getVIsible?) is being reset to False. Have you debugged your code to check the value of the variables you're relying on to enable/disable these tabs/controls/etc?

In connection with the above point, it sounds as there has been a state loss of the global IRibbonUI ribbon object, no? As luck would have it, you're in the right place, because the solution to that problem was found in this forum by MVP RoryA - see Post #7 - https://www.mrexcel.com/board/threads/how-to-preserve-or-regain-the-id-of-my-custom-ribbon-ui.518629/

Whether or not this is relevant to your situation or solves your problem - no idea - would need to see the code.

 

[ACommandLineKindaGuy]

Thanks Dan--problem solved. Just sloppy code

 

[Dan_W]

Ok. Glad you solved it. What was the problem, out of curiosity?

 

[ACommandLineKindaGuy]

Hi Dan,

I had followed the callback to display the faux ribbons with another callback to disable controls on the Fee Tab. Getting rid of that subsequent disable controls callback solved the problem.

I don't quite understand the callback process, but here's what I have:

Option Private Module
Option Explicit

Dim Rib As IRibbonUI
Public MyTag As String
Public YourRibbon As IRibbonUI

#If VBA7 Then
    Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
#Else
    Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
#End If

Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set YourRibbon = ribbon
    ThisWorkbook.Worksheets("Fee Proposal").Range("YourRibbon").Value = ObjPtr(ribbon)
End Sub

#If VBA7 Then
Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
#Else
Function GetRibbon(ByVal lRibbonPointer As Long) As Object
#End If
        Dim objRibbon As Object
        CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
        Set GetRibbon = objRibbon
        Set objRibbon = Nothing
End Function

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

Sub GetEnabled(control As IRibbonControl, ByRef Enabled)
    If MyTag = "Enable" Then
        Enabled = True
    Else
        If control.Tag Like MyTag Then
            Enabled = True
        Else
            Enabled = False
        End If
    End If
End Sub

Sub getPressedRow(control As IRibbonControl, ByRef pressed)
'This sets the checkbox status on startup since by default checkboxes are unchecked on startup
pressed = RowsHidden      'Public variable that is set from a named cell in the workbook
End Sub

Sub getPressedCol(control As IRibbonControl, ByRef pressed)
'This sets the checkbox status on startup since by default checkboxes are unchecked on startup
pressed = ColsHidden      'Public variable that is set from a named cell in the workbook
End Sub

Sub getPressedNotes(control As IRibbonControl, ByRef pressed)
'This sets the checkbox status on startup since by default checkboxes are unchecked on startup
pressed = PrintNotesToo      'Public variable that is set from a named cell in the workbook
End Sub

Sub RefreshRibbon(Tag As String)
    MyTag = Tag
    If YourRibbon Is Nothing Then
        Set YourRibbon = GetRibbon(Worksheets("Fee Proposal").Range("YourRibbon").Value)
        Set SysAccess = ThisWorkbook.Worksheets("Fee Proposal").Range("InSystemAccess")
        YourRibbon.Invalidate
    Else
        YourRibbon.Invalidate
    End If
End Sub

Sub ShowAllIncludingFeeMenu()
    Call RefreshRibbon(Tag:="rib*")     'The faux excel tabs are called ribHome, ribInsert, etc
End Sub

Sub HideAllExceptFeeMenu()
    Call RefreshRibbon(Tag:="Fee*")    'The fee tab is called "Fee Menu"
End Sub

Sub DisableFeeMenu()
    Call RefreshRibbon(Tag:="")
End Sub

Sub EnableFeeMenu()
    Call RefreshRibbon(Tag:="Fee*")      'The control tags that I disable on the Fee Menu Tab are identified as Fee1, Fee2, Fee3, etc
End Sub

Somehow the DisableFeeMenu sub was hiding the ribbon tabs.

Happy to provide more info if you're curious or if you suspect you're close to an answer--if you're inclined to pursue! . LMK.

Regards, John