A Cheat Sheet to get more out of Microsoft Excel
Ever wanted just the Cliff Notes version of Excel? We've got you covered with the Excel Power Users Quick Guide - and it's free.
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 = FalseEnd Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.ErrorCheckingOptions.BackgroundChecking = TrueEnd 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:
Sign up to excel your life with tips, career advice, and more!
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
