Turn off background check in Excel when opening a workbook

Question

I have a lot of green triangles with errors in my Excel workbook.

Is there a way to use Excel VBA so that I can disable it when I open the workbook.

Answer 1

I think this is what you are looking for:

Application.ErrorCheckingOptions.BackgroundChecking = False 

Answer 2

I found the answer that came after:

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

Answer 3

I usually separate workbook tabs into data, calculations, and presentation. So I don’t like the green control triangles for tables on the Presentation tabs. One approach is to protect the leaf ... green checks are leaving! (and only for this tab)

If you still want the protected tab to be accessible, just open all the cells and select the appropriate protection settings before protecting it.

I will not use macros, as this may affect the settings of users in different books and tabs.

Answer 4

Just 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 code above, it disables this future forever for all excel documents.

But if you want to do this only for your excel document (not for everyone), do the following:

  '''''''''''''''' 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 '''''''''''''''''''''''''''''''''''''''''''''''''''''''