Excel - custom menu bar UI freezing & crashing file

[Rijnsent]

I have a big excel file (20 modules, 40 sheets, 5 forms) with a custom menu bar. The main issue I have: after I've run certain macros (got dozens doing various stuff), whenever I switch worksheets, my Excel will freeze, crash and re-open my file.

The custom menu bar has several bits that are only relevant at some sheets. So I've built in some code to switch the menu bar when changing sheets, like so (showing only the relevant part):

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Ws As Worksheet
If Application.ScreenUpdating = True Then
        Set Ws = Sh
        Select Case Sh.name
        Case "Accounts"
            Call RefreshRibbon(ID:="GroupTabAccounts")
        Case Else
            Call RefreshRibbon(ID:="")
        End Select
End if
End Sub

I've already included the condition Screenupdating = True, because I noticed that working from other macros where I would change sheets make the file crash every now and then (yes, I am minimizing the use of the sheet.select statement). So now I switch off screenupdating at the start of my macros, so the custom menu bar doesn't have to refresh needlessly. However, still my excel will freeze quite often after running a macro and than selecting another sheet - the RefreshRibbon is the only event firing in that case.
Does anyone have some pointers what I could check/do to solve this?

 

[Jeffrey Mahoney]

I've had problems with the UI before. The problem was that the Ribbon Object Pointer was lost. I call the RefreshRibbon Sub every time I activate a new sheet. The pointer is saved to a cell on a hidden sheet. My workbook contains hundreds of sheets and many forms and a hug set of macros. The RibbonOnLoad SUB gets called when the workbook is open

Sub RibbonOnLoad(ribbon As IRibbonUI)
  Set rib = ribbon
  Range("ECPSaveRibbon") = ObjPtr(ribbon)
  rib.ActivateTab ("PORTAL")
End Sub

Sub RefreshRibbon()
  If rib Is Nothing Then
    Set rib = GetRibbon(Range("ECPSaveRibbon").Value)
  End If
  rib.Invalidate
  On Error GoTo NoRibbonMenu
  rib.ActivateTab ("PORTAL")
  On Error GoTo 0
  Exit Sub
NoRibbonMenu:
  MsgBox "An error occured while trying to reload the Portal Menu. Reload this workbook"
  On Error GoTo 0
  
End Sub

 

[Rijnsent]

Good point, I do have something like this (with a ObjPtr and an Excel Name) running, but can double check my code.

 

[Jeffrey Mahoney]

I didn't mention that the RibbonOnLoad SUB is automatically run because it's a custom callback for customUI.onLoad. Were you able to add that and test it?

 

[Rijnsent]

Okay, having gone over my code again: I do have a setup like yours -with functions like StoreObjRef, RetrieveObjRef, RibbonOnLoad and RefreshRibbon (not sure where I found them, probably at Ron de Bruins site). So it seems like I'm also doing something to reload a missing menu if that happens. The main difference is that I store the ObjPtr in a Name (named range). Did your workbook also freeze, crash & restart every now and then or would it give an error meessage (the freezing is what my excel does)?

 

[Jeffrey Mahoney]

Ok so that rules that out. Mine did freeze some, but mostly complain about not being able to load the menu.