Excel - custom menu bar UI freezing & crashing file

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,424
Office Version
  1. 365
Platform
  1. Windows
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):
VBA Code:
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?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
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


VBA Code:
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
 
Upvote 0
Good point, I do have something like this (with a ObjPtr and an Excel Name) running, but can double check my code.
 
Upvote 0
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?
 
Upvote 0
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)?
 
Upvote 0
Ok so that rules that out. Mine did freeze some, but mostly complain about not being able to load the menu.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top