Resetting workbookopen code after error

[kgartin]

I have a code set to run when any workbook opens. It's set on Personal>ThisWorkbook.

Works flawlessly until there's any kind of error and then I have to close and reopen the Excel app to get it to work again. Is there another way to reset without restarting the app?


Thanks!

 

[Joe4]

What kind of errors are you getting?
What does the code look like?
You may be able to add error handling to your code to gracefully handle them.

 

[kgartin]

What kind of errors are you getting?
What does the code look like?
You may be able to add error handling to your code to gracefully handle them.

The error code isn't associated with the WorkbookOpen code. It can be any error at all that causes the code to no longer function when a workbook opens until the app is closed and restarted.

Here's the full code that may help:

Private Sub app_WorkbookOpen(ByVal WB As Workbook)
WBC = Application.Workbooks.COUNT


Dim JOBNUM As Integer, JROW As Integer


If WBC < 3 Then Exit Sub


'If ActiveWorkbook.ActiveSheet.Range("B4") = "LAYOUT" Then GoTo Skip


'Dim WB As Workbook
On Error Resume Next
    Set WB = Workbooks(Year(Date) & " Com Forecast (544).xlsm")
    If Err Then Exit Sub 'MsgBox "The workbook is not open"
    If WB.Worksheets("START DATE").Range("R7") = False Then Exit Sub
On Error GoTo 0
 
Skip:
    'If ActiveWorkbook.ActiveSheet.Range("B4") = "LAYOUT" Then Call SAVE_COLORPOS
    If ActiveWorkbook.ActiveSheet.Range("K2") = "Person In Charge" Then Call COPYPREP_COPY
    If ActiveWorkbook.ActiveSheet.Range("B1") = "COMMERCIAL DISTRIBUTION INFORMATION REPORT" Then Call DIST_COPYPREP_COPY
    If ActiveWorkbook.ActiveSheet.Range("B4") = "Commercial Billing Sheet" Or ActiveWorkbook.ActiveSheet.Range("D21") = "Sold To:" Then
        Application.Windows.Arrange ArrangeStyle:=xlHorizontal
        If Range("A3") = "" Then GoTo Getout
        JOBNUM = Range("A3").Value
        WB.Activate
        JROW = Application.Match(JOBNUM, WB.Worksheets("YTD").Range("E:E"), 0) - 2
        ActiveWindow.ScrollRow = JROW
    End If
    
Getout:
End Sub

 

[kgartin]

Any ideas?

 

[Joe4]

Sorry, I did not realize that you were working with Application events (and not Workbook events), and I do not really have any experience with them.
I am not sure if it helps, but I found this here: http://www.cpearson.com/excel/AppEvent.aspx

 

[Norie]

How exactly does it stop working?

Is the code not being executed the next time you open a workbook?