Rijnsent
Well-known Member
- Joined
- Oct 17, 2005
- Messages
- 1,424
- Office Version
- 365
- Platform
- 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):
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?
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
Does anyone have some pointers what I could check/do to solve this?