Turning error checking off and back on again for the current file

[Earthsea]

Dear All,

Background: I am an experienced programmer but a novice with Excel macros and VBA so please bear with me.

Summary: I have a situation where I want to turn error checking off and back on again for specific Excel files but not for all files i.e. not for the entire 'Excel' application.

Details: in ThisWorkbook I have the following private subroutines:

Private Sub Workbook_Open()
MsgBox ("Error checking OFF in Workbook_Open()")
Application.ErrorCheckingOptions.NumberAsText = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox ("Error checking ON in Workbook_BeforeClose()")
Application.ErrorCheckingOptions.NumberAsText = True
End Sub

They work fine. Here's the problem: these private subs are in an Excel file that we'll call A.xlsx. I make two new files - B and C - by copying A. If I open B the error checking is turned off correctly. If I then open C the error checking is once again turned off correctly. BUT if I then close C the error checking is turned back on again - and not just for C but also for file B. What I would like is for the error checking to remain off within B until it too is closed. If I were to open an unrelated file D I would want the error checking in that to be on as it should be by default.

Even though I am using the 'Application' object I had presumed that having the private subs in ThisWorkbook would limit their scope but I think that I'm starting to see that I may have misunderstood.

Is it possible to do what I am trying to do? Since the attribute ErrorCheckingOptions does not exist for the object ActiveWorksheet (which I tried on a hunch) I think that the answer may be 'No'...

Thank you.

 

[Jerry Sullivan]

Hi and Welcome to the Board,

As you've discovered, changing the Application object affects the User interface for all Workbooks in that instance of Excel.

If you want to control this separately for different Workbooks you might consider using the Workbook_Activate Event to trigger the toggle instead of Workbook_Open...

Private Sub Workbook_Activate()
    MsgBox "Error checking OFF in" & ActiveWorkbook.Name _
        & " Workbook_Activate()"
    Application.ErrorCheckingOptions.NumberAsText = False
End Sub

Private Sub Workbook_Deactivate()
    MsgBox "Error checking ON in" & ActiveWorkbook.Name _
        & " Workbook_Deactivate()"
    Application.ErrorCheckingOptions.NumberAsText = True
End Sub

 

[Earthsea]

That is perfect. Thank you very much!