How to restore RibbonUI if two files open simultaneously?

Question

I have below code to restore RibbonUI where RibbonUI randomly lost its value aka it becomes nothing. This code works well if the file which contains this code open in single. But if I open the copy of this file then the excel crashes/closes in the CopyMemory function line when I checked in the debug mode.

#If VBA7 Then
  Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpDest As Any, lpSource As Any, ByVal cBytes&)
#Else
  Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (lpDest As Any, lpSource As Any, ByVal cBytes&)
#End If
Public Template_Rib As IRibbonUI
Public Sub CallbackOnLoad(ribbon As IRibbonUI)
#If VBA7 Then
  Dim StoreRibbonPointer As LongPtr
#Else
  Dim StoreRibbonPointer As Long
#End If
Set Template_Rib = ribbon
Template_Rib.ActivateTab "TemplateTab" 'Name of the tab to activate
'Store pointer to IRibbonUI in a Named Range within add-in file
StoreRibbonPointer = ObjPtr(ribbon)
ThisWorkbook.Names.Add Name:="RibbonID", RefersTo:=StoreRibbonPointer
End Sub
Sub TryToRetrieveRibbon()
On Error GoTo ErrorHandler
If Template_Rib Is Nothing Then
  Set Template_Rib = GetRibbon(Replace(ThisWorkbook.Names("RibbonID").RefersTo, "=", ""))
End If
ErrorHandler:
Err.Clear
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

Edit:

I have given some sample dummy files for your reference to explain the issue more clearly. When we click "Custom Button" in "Custom Tab" of these xlsm files in the link I gave, the Ribbon callback is only being called from the first file we open. I think this behavior of the Ribbon is the root cause of the problem. Can we fix this issue by making the Ribbon call the callback function of the respective file?

Answer 1

I have a Workbook with a similar process. Reading your question, I opened the book three times - with a different name of course - and it didn't present any problems. I noticed that we have a slight difference in how we retrieve the RibbonUI. I am posting the code snippets for you to try.
In the book, manually I have already created a Name, named "_RIBBON_OBJ" with book scope.
The code is tested on Office2007 and Office2019.

Option Explicit

'- - - - - RIBBON - - - - - -
Public Const RIBBON_OBJ = "_RIBBON_OBJ"
Public ExcelRibbon As IRibbonUI

' ------------------Declarations---------------------
#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

'-------------------GetObjectPointer------------------
#If VBA7 Then
Public Function GetObjectPointer(ByVal lPointer As LongPtr) As Object
#Else
Public Function GetObjectPointer(ByVal lPointer As Long) As Object
#End If
   Dim objct As Object
   CopyMemory objct, lPointer, LenB(lPointer)
   Set GetObjectPointer = objct
   Set objct = Nothing
End Function


Public Sub RibbonOnLoad(ribbon As IRibbonUI)
   Set ExcelRibbon = ribbon
   ThisWorkbook.Names(RIBBON_OBJ).Value = ObjPtr(ribbon)
   ' ... .... ...
End Sub

' --------HERE I AM USING Evaluate ON the value of stored ribbon object-------
Public Function IsRibbonOk() As Boolean
   If ExcelRibbon Is Nothing Then
       Set ExcelRibbon = GetObjectPointer(Evaluate(ThisWorkbook.Names(RIBBON_OBJ).Value))
   End If
   IsRibbonOk = Not ExcelRibbon Is Nothing
End Function

CONTINUATION OF THE ANSWER 11-07-2023
I downloaded the "sample dummy files" and noticed that in the Workbook_Open of each file you open the same xlam that you just renamed. Why do this; In the xlam files we write code that will be common to all books. I have not dealt with xlam files but it is not correct to load the same xlam twice regardless if you renamed it since internally they have the same Id for the tab: id="customTab" label="Custom Tab". How will excel distinguish from one another? since they are intended to be common to all books; So if you load Book.xlam from within Book1.xlsm, then you don't need to reload it from Book2.xlsm. You will see the "Custom Tab" in all the books, by clicking on the "Custom button" to show you the correct name you must change the command from: MsgBox ThisWorkbook.Name to: MsgBox ActiveWorkbook.Name. Always consider that the xlam UI will act on the ActiveWorkbook. If you want to load an xlam file through some of your own books, then you can use the following code to load it only if it has not already been loaded from another book:

Private Sub Workbook_Open()
   Const xlamName = "Book.xlam"
   If isXLAMopen(xlamName) = False Then
      'MsgBox "Will open " & xlamName
      Workbooks.Open ThisWorkbook.Path & "\" & xlamName
   End If
End Sub


Function isXLAMopen(addinName As String) As Boolean
   Dim i As Long, wb As Workbook
   
   For i = 1 To Application.AddIns2.Count
      If Application.AddIns2(i).Name = addinName Then
         isXLAMopen = True
         Exit Function
      End If
   Next
End Function