CODEWITHSUNDEEP
vbaexcel
Craig T
Craig T

Reputation: 2752

Turn off Excel Background Error Checking on opening the workbook

I have an excel workbook was has lots of the green "error checking" triangles.

Is there any way using Excel VBA that I can this off when I open the workbook.

Upvotes: 8

Views: 17232

Answers (5)

Fleischbender
Fleischbender

Reputation: 21

13 years late, but this is you can make your own iferror-statement in VBA without the sub stopping:

On Error Resume Next
    'MyCode
If Err.Number > 0 Then
    'Code if error
Else
End If
On Error GoTo 0

Upvotes: -2

Krisha
Krisha

Reputation: 21

I usually split my workbook tabs into Data, Calculations and Presentation. As such, I don't like the green error checking triangles for tables within my 'Presentation' tabs. One approach is to protect the sheet...the green checks go away! (and only for that tab)

If you still want the protected tab to be accessible then just unlock all the cells and select the appropriate protection options prior to protecting it.

I'd stay away from using macros as this may affect the users settings across various workbooks and tabs.

Upvotes: 2

Mahdi Jazini
Mahdi Jazini

Reputation: 801

Simply use this:

With Application.ErrorCheckingOptions
    .BackgroundChecking = False
    .EvaluateToError = False
    .TextDate = False
    .NumberAsText = False
    .InconsistentFormula = False
    .OmittedCells = False
    .UnlockedFormulaCells = False
    .ListDataValidation = False
End With

If you use the above code, it turns off this future forever and for all excel documents.

But If you would like to do it just for your excel document (not for all) do this:

    '''''''''''''''' IN A MODULE '''''''''''''''''''
    Public AE_BackgroundChecking As Boolean
    Public AE_EvaluateToError As Boolean
    Public AE_TextDate As Boolean
    Public AE_NumberAsText As Boolean
    Public AE_InconsistentFormula As Boolean
    Public AE_OmittedCells As Boolean
    Public AE_UnlockedFormulaCells As Boolean
    Public AE_ListDataValidation As Boolean
    Public AE_EmptyCellReferences As Boolean
    ''''''''''''''''''''''''''''''''''''''''''''''''

    ''''''''''''''''' IN WORKBOOK OPEN EVENT '''''''''''''

    AE_BackgroundChecking = Application.ErrorCheckingOptions.BackgroundChecking
    AE_EvaluateToError = Application.ErrorCheckingOptions.EvaluateToError
    AE_TextDate = Application.ErrorCheckingOptions.TextDate
    AE_NumberAsText = Application.ErrorCheckingOptions.NumberAsText
    AE_InconsistentFormula = Application.ErrorCheckingOptions.InconsistentFormula
    AE_OmittedCells = Application.ErrorCheckingOptions.OmittedCells
    AE_UnlockedFormulaCells = Application.ErrorCheckingOptions.UnlockedFormulaCells
    AE_ListDataValidation = Application.ErrorCheckingOptions.ListDataValidation
    AE_EmptyCellReferences = Application.ErrorCheckingOptions.EmptyCellReferences

    With Application.ErrorCheckingOptions
        .BackgroundChecking = False
        .EvaluateToError = False
        .TextDate = False
        .NumberAsText = False
        .InconsistentFormula = False
        .OmittedCells = False
        .UnlockedFormulaCells = False
        .ListDataValidation = False
        .EmptyCellReferences = False
    End With
    ''''''''''''''''''''''''''''''''''''''''''



''''''''''''''''' IN WORKBOOK CLOSE EVENT '''''''''''''
Application.ErrorCheckingOptions.BackgroundChecking = AE_BackgroundChecking
Application.ErrorCheckingOptions.EvaluateToError = AE_EvaluateToError
Application.ErrorCheckingOptions.TextDate = AE_TextDate
Application.ErrorCheckingOptions.NumberAsText = AE_NumberAsText
Application.ErrorCheckingOptions.InconsistentFormula = AE_InconsistentFormula
Application.ErrorCheckingOptions.OmittedCells = AE_OmittedCells
Application.ErrorCheckingOptions.UnlockedFormulaCells = AE_UnlockedFormulaCells
Application.ErrorCheckingOptions.ListDataValidation = AE_ListDataValidation
Application.ErrorCheckingOptions.EmptyCellReferences = AE_EmptyCellReferences
'''''''''''''''''''''''''''''''''''''''''''''''''''''''

Upvotes: 0

Marc Thibault
Marc Thibault

Reputation: 1728

I think this is what you're looking for:

    Application.ErrorCheckingOptions.BackgroundChecking = False

Upvotes: 10

Craig T
Craig T

Reputation: 2752

I found the answer that I was after:

Sub Auto_Open()
    Application.ErrorCheckingOptions.BackgroundChecking = False 
End Sub

Upvotes: 4

Related Questions