Issue with sequence of events: Workbook_open, Workbook_sheetActivate, and custom Ribbon

If you think it is a timing issue, have you tried inserting a delay at the begiinning of your ribbon code?

w = Timer + 2
Do While Timer < w
    DoEvents
Loop

This would give a 2 second delay before executing the rest of the code.

Or as you alluded to in your post, call the ribbon sub from your Workbook_Open event.

Thanks for the feedback,
Regards, JLG

Hi all,

This is a follow up on the issue which I is now fixed (so I believe). I wanted to create a ribbon that would update based on the worksheet activated. I faced two problems:

1. 1. Upon opening the workbook, there were actions prior loading the ribbon which was issue
2. 2. Any VBA code error caused an issue with the customized ribbon because Excel was losing the ribbon id

I was able to fix first problem by creating a global variable as a flag. Nevertheless, this fix was not general enough. However, I was able to find a fix for the second one that was developed by RoryA (MrExcel MVP. Moderator) with a workbook made available by WernerGg. I cannot paste the link, but you can find the discussion by conducting a search for "How to preserve or regain the Id of my custom ribbon UI?"

The basic idea is that Excel some times loses the ribbon id. The approach uses a cell in the workbook to store the ribbon id which can be used to refresh the ribbon. However, the solution proposed needed some tweaking because it requires to be updated for 64 bit version of Excel.


Module ObjectStore

Option Explicit
Private Const C_OBJ_STORAGENAME As String = "thisWorkbook_IRibbonUI_Ptr"

Private Declare PtrSafe Sub CopyMemory Lib "kernel32" _
    Alias "RtlMoveMemory" (destination As Any, source As Any, ByVal length As Long)

Public Function StoreObjRef(obj As Object) As Boolean
' Serialize and savely store an object reference

    StoreObjRef = False
    
    ' Serialize
    Dim longObj As LongPtr
    longObj = ObjPtr(obj)
    
    ' Store into a defined name
    If IsNumeric(Range(C_OBJ_STORAGENAME)) Then
        Range(C_OBJ_STORAGENAME) = longObj
        Debug.Print "Save storage """; C_OBJ_STORAGENAME; """ stored the object reference"; longObj
    End If
    
    ' Return
    StoreObjRef = True
End Function

Public Function RetrieveObjRef() As Object
' Retrieve from save storage, deserialize and return the object reference
' stored with StoreObjRef

    Set RetrieveObjRef = Nothing
    
    ' Retrieve from a defined name
    Dim longObj As LongPtr
    If IsNumeric(Range(C_OBJ_STORAGENAME)) Then
        longObj = Range(C_OBJ_STORAGENAME)
        MsgBox "Let's see if we are now ok with the ribbon."
        
        Debug.Print "Object reference"; longObj; "retrieved from save storage """; C_OBJ_STORAGENAME; """"
        
        ' Deserialize
        Dim obj As Object
        CopyMemory obj, longObj, 16
        
        ' Return
        Set RetrieveObjRef = obj
        Set obj = Nothing
    End If
End Function

Private Sub use_NameWithoutRef()
' Just demonstrate how a name with no reference would be used

    Const C_OBJ_STORAGENAME_NOREF As String = "foo"
    Dim aName As name, longObj As Long

    ' On each access check if the name exists.
    ' If not, create it with no reference to a cell and value 0
    With ThisWorkbook
        On Error Resume Next
        Set aName = .Names(C_OBJ_STORAGENAME_NOREF)
        On Error GoTo 0
        If aName Is Nothing Then
            Set aName = .Names.Add(name:=C_OBJ_STORAGENAME_NOREF, RefersTo:=0)
        End If
    End With

    ' store some Long under that Name
    longObj = Timer
    aName.Value = longObj   ' Value is "=4711"
    Debug.Print "Save storage """; C_OBJ_STORAGENAME_NOREF; """ stored the object reference"; longObj
    
    ' retrieve some Long from that Name
    longObj = Mid(aName.Value, 2)
    Debug.Print "Object reference"; longObj; "retrieved from save storage """; C_OBJ_STORAGENAME_NOREF; """"
End Sub

Option Explicit

Dim Rib As IRibbonUI
Public MyTag As String
Public OpenWkb As Integer

Private Const C_INTERRUPT As String = "thisWorkbook_Interrupt"


'Callback for customUI.onLoad
Sub RibbonOnLoad(ribbon As IRibbonUI)
    Set Rib = ribbon
    If Not StoreObjRef(Rib) Then Beep: Stop
End Sub

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 RefreshRibbon(Tag As String)
    MyTag = Tag
    
    ReloadRibbon

End Sub



Private Function ReloadRibbon(Optional id As String = "") As Boolean
' Force the ribbon UI to reload so that states are refreshed.
' This is done by an Invalidate or InvalidateControl(id)
' Returns True if successful
       
    
    ReloadRibbon = False
    
    ' Invalidate the ribbon UI so that everything gets reloaded
    If Not (Rib Is Nothing) Then
        ' Invalidate will force the UI to reload and thereby ask for current states
        If Len(id) > 0 Then
            Rib.InvalidateControl id ' Note: This does not work reliably
        Else
            Rib.Invalidate
        End If
        ReloadRibbon = True
        Exit Function
    Else
        ' The static guiRibbon-variable was meanwhile lost.
        ' We try to retrieve it from save storage and retry Invalidate.
        On Error GoTo GiveUp
        Set Rib = RetrieveObjRef()
        If Len(id) > 0 Then
            Rib.InvalidateControl id ' Note: This does not work reliably
        Else
            Rib.Invalidate
        End If
        On Error GoTo 0
        ReloadRibbon = True
        Exit Function
GiveUp:
        MsgBox "Due to a design flaw in the architecture of the MS ribbon UI you have to close " & _
            "and reopen this workbook """ & ThisWorkbook.name & """." & vbNewLine & vbNewLine & _
            "Very sorry about that." & vbNewLine & vbNewLine & _
            "Clear the colored cell H2 on sheet IRibbonUI, save and close the workbook and try again. " & _
            "You will then have no problems anymore." _
            , vbExclamation + vbOKOnly, ThisWorkbook.name & ".ReloadRibbon"
        ' Note: In the help we can find
        ' Rib.Refresh
        ' but unfortunately this is not implemented.
        ' It is exactly what we should have instead of that brute force reload mechanism.
    End If
    
End Function

Kindest regards,

Dominic.