ErrorCheckingOptions - Do these options reset automatically? 1

[MattGreer]

Not sure I phrased it correctly in my subject line.

I was wanting to be able to set the formula error checking options on a certain workbook, but not on ALL workbooks. I found this object, ErrorCheckingOptions but I'm not sure I understand its application (no pun intended, see what I did there lol).

I tested and it seems that if I set the error checking options via VBA in one workbook, the settings are persistent the next time I open Excel. I set one of the options like this:

Sub SetErrorOptions()

    Application.ErrorCheckingOptions.InconsistentFormula = False

End Sub

I then closed the workbook and then opened a new blank workbook, setting was the same, and then opened a few other workbooks, one 10 years old (still an xlsx) and the setting was still false.

Question
Do the formula error checking options ever reset automatically? If so, when?


Thanks!!


Matt

 

[combo]

You can set error checking options via Excel UI (options, formulas). They refer to whole application, so all workbooks. What you do is setting inconsistent formula behaviour by code.

combo

 

[MattGreer]

Yeah, I know you can set it through Excel.

What I'm thinking about doing is setting it for a particular workbook when it's opened, and then setting it back when it's closed. That being said, I know I've changed the options via the GUI before, but I think... I think... they somehow returned to a "default" setting somehow at some point in time. Not sure.

I was just wondering if anyone knew of a triggering event that would reset the formula checking options.

Thanks!!


Matt

 

[combo]

This can be set and reset in Workbook_Open and Workbook_BeforeClose events. However, in the meantime the setting will affect all workbooks.
As an alternative, you can protect the sheet, error markers are not displayed in protected sheet, even for not locked cells.

combo

 

[MattGreer]

Good to know, thank you for that.

Wish I could set it at the workbook level, meh. I hate those notifications tho, but then again they can be useful at times.

Thanks!!


Matt