Turn Off Cell Background Error Checking with VBA

August 1, 2012
/ Tips VBA
/ By Jordan Goldmeier

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