CODEWITHSUNDEEP
vbaexcel
George W
George W

Reputation: 133

Automation Error - Catastrophic Failure EXCEL VBA

I have a workbook which is throwing this error on opening. When it does and I open the VBA module, the current line is the definition of a sub. But the only option is to kill the whole Excel process.

I've got custom document properties, I've got embedded combo-box controls, I have no clue what it might be, and Excel isn't helping.

However, when I open the same file on another computer - it doesn't throw the error.

Does anyone have any experience or advice with this kind of error?

Here's the Open code, but the 'Show Next Statement' command doesn't point here when the error occurs:

````

Private Sub Workbook_Open()
    Dim ans


    If Range("currentstatus") Like "*Ready for Year-End Preparation*" Then
        ans = MsgBox("This workbook is ready for Year-End Preparation" & vbCrLf & "Would you like to begin?", vbYesNo)

        If ans = vbYes Then
            Range("Phase") = "Year-End"
            SheetsSet 3
        End If
    End If

    'Exit Sub

    If Range("Phase") = "Commissions" Then

        If Range("currentstatus") Like "*RVP/Dept Head Approved*" Then
            ans = MsgBox("Commissions have been approved for " & Range("applicablemonth") & vbCrLf & "Would you like to enter data for the new period?", vbYesNo + vbQuestion)
            If ans = vbYes Then


                Range("ApplicableMonth") = Format(DateAdd("m", 1, CVDate(Range("applicablemonth"))), "YYYY-MM")
                Range("CurrentStatus") = "Ready for Data Entry for " & Range("ApplicableMonth")

                ' now reset the summary page
                Prot False, "Commission Form Summary"
                Range("SalesPersonComplete") = Range("Summary")
                Range("RVPComplete") = ""
                Range("BrMgrComplete") = ""
                Prot True, "Commission Form Summary"

                Sheets("Menu").Select
                '                MsgBox "Begin."
            End If
        End If


    End If

End Sub

Upvotes: 4

Views: 38887

Answers (3)

Boketto
Boketto

Reputation: 895

This sounds like a Voodoo procedure, but what helps when I got this error is to edit any of the VBA code (for example in some module add a linebreak and remove it) and then save the workbook. Maybe it's some kind of caching issue in my case but I thought it might help some of you too.

Upvotes: 1

NathanDetr0it
NathanDetr0it

Reputation: 11

Double-check your file extension. Excel spreadsheets with macros embedded need a *.xlsm extension, not *.xls.

Total 'for-dummies' answer, but I just made this mistake myself.

Upvotes: -1

Chris Mack
Chris Mack

Reputation: 5208

I had this message earlier today and it was due to another instance of Excel being open as a background process (the background process had previously opened the file in question, so it must have been something to do with that). Once I closed the other instance the problem disappeared.

It might be worth checking 'Task Manager' > 'Background processes' to see if that's the case.

Upvotes: 4

Related Questions