Turn Off Cell Background Error Checking with VBA

Home
/
Blog
/
Excel VBA & Macros
/
Turn Off Cell Background Error Checking with VBA

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

Author
Recent Posts

Jordan Goldmeier

Jordan Goldmeier is an accomplished data professional with a wealth of experience across various industries. He currently serves as a consultant at Anarchy Data, where he assists businesses in maximizing the capabilities of Excel for financial planning and analysis. Jordan is also an instructor at Full Stack Modeller and a former Adjunct Instructor in Analytics at Wake Forest University. His extensive career has seen him hold positions as the Chief Operations Officer at Excel.TV, Data Science Manager at DataKind, Data Scientist at Dealer Tire and EY, Analytics & Data Vis Developer at The Perduco Group, and Operations Research Analyst at Booz Allen Hamilton. Jordan's background in data analytics and his passion for Excel make him a valuable resource for businesses seeking to improve their data-driven decision-making processes.

Latest posts by Jordan Goldmeier (see all)

All Excel LOOKUPs Explained - May 26, 2020
How to: Power Query File From Folder - April 21, 2020
Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020

Turn Off Cell Background Error Checking with VBA

You may also like

34: Jon Acampora – ExcelCampus.com – VBA Pro Course

My Top 5 VBA Development Environment Tips

A VBA Coding Manifesto (Part 1)

A VBA Coding Manitesto (Part 2)

How To Run Macros In Protected Worksheets – Excel VBA Tips

Excel Holiday Card

How To Use ChatGPT with Excel

How to Remove Apostrophe in Excel the Quick Way

How to Cut a Cell Value in Excel: 7 Techniques

How to Unsort in Excel: Get Back to Your Organized Spreadsheet in No Time

Free Power User Quick Guide