August 1

Turn Off Cell Background Error Checking with VBA

0 comments

When I’m all done with my Excel application, I’ll usually want to flip off background error checking – that is, I’ll tell Excel to stop showing those little green triangles that appear in cells. Don’t get me wrong, those little green alerts can be useful – but they are rarely so in a finished product where I know my layout and formulas are correct. I just want to tell Excel, “thanks for the help, but stop annoying me already!” Indeed, these green alerts appeared on other computer screens when users opened my Periodic Table of elements file, which I found annoying.

The problem is that while I can tell those green triangles to go away on my instance Excel by going into Excel Options (or simply by clicking “ignore”), that won’t fix the problem when my file is loaded onto other computers. The way around this is some VBA and the workbook open and close events.

So, in my ThisWorkbook object in the VBA window, I wrote this:

Option Explicit

Private Sub Workbook_Open()

Application.ErrorCheckingOptions.BackgroundChecking = False

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.ErrorCheckingOptions.BackgroundChecking = True

End Sub

There are actually several types of background error checking that go on in a workbook. Excel allows you to disable these separate error checks by themselves if don’t want to disable everything. Specifically, you can modify background checking options for empty cell references, error calculations, inconsistent formulas, and omitted cells, among others. For example, you might simply write:

Application.ErrorCheckingOptions.OmittedCells = False

if you only want Excel to stop monitoring for formula patterns that appear to omit cells that Excel thinks should be included in the formula. This will work so long as BackgroundChecking is still True. If you set BackgroundChecking to False like in the example above, Excel will cease all attempts to second guess your work (which can make your life easier, sometimes).

To read more, see:

The ErrorCheckingOptions Object

Edit Excel Formulas in WORD – Spreadsheet Tips and Tricks

February 8, 2021

51: Oz du Soleil & the Global Excel Summit 2021

January 29, 2021

Global Excel Summit 2021

January 22, 2021

50: Randy Austin – Excel for Freelancers

January 8, 2021

49: Theresa Estrada – Microsoft Principal Program Manager Lead

May 26, 2020

All Excel LOOKUPs Explained

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.

Get Your FREE Excel Power User's Quick Guide Now