This is, unfortunately, to be expected. Every time there is an error, the RibbonX object goes out of scope, and we can't get it back. The only way to do so is to re-open the workbook.
Rory Archibald did come up with a method to store a pointer to the Ribbon object though, which looks like this:
Code:
Public Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (destination As Any, source As Any, _
ByVal length As Long)
Public Sub ribbonLoaded(ribbon As IRibbonUI)
' Store pointer to IRibbonUI
Dim lngRibPtr As Long
' Store the custom ribbon UI Id in a static variable.
' This is done once during load of UI. I.e. during workbook open.
Set guiRibbon = ribbon
lngRibPtr = ObjPtr(ribbon)
' Write pointer to worksheet for safe keeping
Tabelle2.Range("A1").Value = lngRibPtr
End Sub
Function GetRibbon(lngRibPtr as Long) As Object
Dim objRibbon As Object
CopyMemory objRibbon, lngRibPtr, 4
Set GetRibbon = objRibbon
Set objRibbon = Nothing
End Function
You'd then use the following code to restablish a link to the ribbon when it dies:
Code:
Public Sub DoButton(ByVal control As IRibbonControl)
' The onAction callback for btn1 and btn2
' Toggle state
Toggle12 = Not Toggle12
' Invalidate the ribbon UI so that the enabled-states get reloaded
If Not (guiRibbon Is Nothing) Then
' Invalidate will force the UI to reload and thereby ask for their enabled-states
guiRibbon.Invalidate 'Control ("tabCustom") InvalidateControl does not work reliably
Else
Set guiRibbon = GetRibbon(CLng(Tabelle2.Range("A1").Value))
guiRibbon.Invalidate
' The static guiRibbon-variable was meanwhile lost
' MsgBox "Due to a design flaw in the architecture of the MS ribbon UI you have to close " & _
' "and reopen this workbook." & vbNewLine & vbNewLine & _
' "Very sorry about that.", vbExclamation + vbOKOnly
MsgBox "Hopefully this is sorted now?"
' Note: In the help we can find
' guiRibbon.Refresh
' but unfortunately this is not implemented.
' It is exactly what we should have instead of that brute force reload mechanism.
End If
End Sub
Rory's original post can be found here
Bookmarks