Workbook with Ribbon keeps crashing Excel

Question

I have a fairly big workbook with a very complex VBA project.

The workbook also has a custom-ribbon...

There is quite alot happening in the Workbook_Open event too, unprotecting and reprotecting (userInterfaceOnly), hiding and showing various sheets, storing a reference to the ribbon etc.

When macros are not automatically enabled, the workbook opens, the enable button appears and when I click it everything happens as it should.

The problem arises when the file is trusted, and the macros run automatically. In these circumstances, it has a tendency to crash the application.

It's as though, if there is a pause where the user has to click the button, it does all its Excel preparations, displays the ribbon and THEN does all my Workbook_Open stuff, but if there is no enable button somehow Excel's own startup stuff and displaying the ribbon seems to get tangled with my Workbook_Open event, causing it to crash.

This happens in 2007, 2010 and 2013 and on both windows 7 & 8(.1)

I suspect that it is something to do with the ribbon, because sometimes the problem isn’t a full crash, but instead, the workbook opens, with the ribbon area just blank.

FYI the code in the Workbook_Open event looks like this:

enter code here
Private Sub Workbook_Open()
Dim newHour As Single
Dim newMinute As Single
Dim newSecond As Single
Dim waitTime As Date

    Application.ScreenUpdating = False
'   To activate the ribbon in v2007
    Application.SendKeys "%Q{RETURN}"

    Call ResetTheSheets

    Splash.Visible = xlSheetVisible
    Splash.Protect shtHidden.Range("iWord")
    Call ShowHideSplash

    ExecSumm.Visible = xlSheetVisible
    GetStarted.Visible = xlSheetVisible
'   Look at the properties in the Prop sheet to see what should be displayed
    If Prop.Range("PropHideGetStarted") Then

On Error Resume Next
        With ExecSumm
            .Activate
            'Range("C4").Select
            .Unprotect Password:=shtHidden.[iWord]
            .Visible = xlSheetVisible
            .Range("A1:A" & .Range("LastRowCol").Row).EntireRow.Hidden = False
            .Range(.Cells(1, 1), .Cells(1, .Range("LastRowCol").Column)).EntireColumn.Hidden = False
'           Check to see if the template column has anything in
            If WorksheetFunction.CountA(.Range("TemplateCol")) = 0 Then .Range("TemplateCol").EntireColumn.Hidden = True
'           Check to see if the prior year should be hidden
            If Not Prop.Range("PropBFbalances") Then .Range("ppTitle").EntireColumn.Hidden = True
            .Activate
        End With
    Else
        GetStarted.Select
    End If
    ActiveWindow.ScrollRow = 1

FormShow:
Err.Clear
On Error GoTo FormShow
    frmSplash.Show msoFalse

    shtHidden.Range("iBalance") = "TRUE"
'   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
''   Schedule the start to avoid 'startup-crashes' - this didn't work as a way to prevent
'    newHour = Hour(Now())
'    newMinute = Minute(Now())
'    newSecond = Second(Now()) + 1
'    waitTime = TimeSerial(newHour, newMinute, newSecond)
'    Application.Wait waitTime
'   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Err.Clear
On Error GoTo 0
    Application.OnTime Now + TimeValue("00:00:01"), "StartUpMacro"
End Sub

Have you tried moving *all* your Open code to the StartUpMacro routine? — Rory, Oct 07 '14 at 12:05

score 0 · Answer 1 · edited May 23 '17 at 12:20

0

You may try:

a. let excel wait for predefined time to get ready before or after the sendkey thing as per your need:

Application.Wait(Now + #0:00:05#)      '5 sec for example

Courtesy: link

b. using a msgbox to popup (similar to macro enable button) to inhibit quick execution of rest of the code.

edited May 23 '17 at 12:20

Community

1
1

answered Oct 07 '14 at 09:32

ZAT

1,347
7
10

Workbook with Ribbon keeps crashing Excel

1 Answers1