Conditional formatting =NOT(HasFormula(B4)) prevents Workbook_Open() event from firing

[vwhite]

Hi All

I have quite an interesting little conundrum here

I like to use the following VBA in many of my workbooks, in combination with a formula in the conditional formatting to change the background colour of any cells depending on whether they contain a formula

VBA

Please Login or Register  to view this content.

Conditional Formatting:
=NOT(HasFormula(B4))

But it would appear using this formula in the condition formatting somehow stops the Workbook_Open() event from firing?

You can have one but not both. See attached workbook to test.

A MsgBox saying 'hello' should appear on opening. But it won't fire until the conditional formatting is turned off

Perhaps this is an Excel bug?

This person seemed to find a workaround in their last post, but I don't really understand their answer
https://www.vbforums.com/showthread....vent-won-t-run

If anyone can suggest another way to highlight cells in a table that don't contain a formula without using the above method it would be a great help.

All the best
V

[Bernie Deitrick]

No solution for your stated problem, but rather a suggestion. When I create a formula, I format the cell blue. When I have a cell that requires a constant to the entered, I format the cell green. Headers are tan, row labels are another color, etc. Unless you have cells that will suprise you by converting themselves from blanks or values to formulas, then I really don't see a need for the CF that you are using. If you want to fix existing workbooks, then use a macro, which will also cut down on unnecessary overhead.

Please Login or Register  to view this content.

[Greg M]

Hi there,

For what it's worth!

Using Excel 2013, when I opened your workbook directly from your post the "Hello" message box was displayed. I repeated this step several times.

I downloaded your file to my hard drive, opened it using File Explorer and the "Hello" message box was displayed.. I repeated this step several times.

I then saved the file, closed it, and re-opened it using File Explorer and the "Hello" message box was NOT displayed.

I know this isn't a solution, but perhaps it's a bit of diagnostic information which might be useful to someone who knows how to interpret it.

Regards,

Greg M

[vwhite]

Hi there,

For what it's worth!

Using Excel 2013, when I opened your workbook directly from your post the "Hello" message box was displayed. I repeated this step several times.

I downloaded your file to my hard drive, opened it using File Explorer and the "Hello" message box was displayed.. I repeated this step several times.

I then saved the file, closed it, and re-opened it using File Explorer and the "Hello" message box was NOT displayed.

I know this isn't a solution, but perhaps it's a bit of diagnostic information which might be useful to someone who knows how to interpret it.

Regards,

Greg M

Hi Greg

I cannot replicate the same, but perhaps that's because I'm using Excel 2010? I'm also on Citrix which I guess could effect it?
Interesting info to know though, thank you

[vwhite]

No solution for your stated problem, but rather a suggestion. When I create a formula, I format the cell blue. When I have a cell that requires a constant to the entered, I format the cell green. Headers are tan, row labels are another color, etc. Unless you have cells that will suprise you by converting themselves from blanks or values to formulas, then I really don't see a need for the CF that you are using. If you want to fix existing workbooks, then use a macro, which will also cut down on unnecessary overhead.

Please Login or Register  to view this content.

Hi Bernie

Thanks for this. We use the conditional formatting in the summary tables in our SLA reports.
All the figures in the summary table are pulled through by formula

90% of the time this produces the right value. But occasionally they need to be manually over-typed
This poses a bit of a risk the following month when someone else picks the report up to refresh it, but they won't know the value for January (lets pretend) is static and not updating when they refresh the query.
So I use the conditional formatting to highlight straight away any cells where the formulas have been removed

[vwhite]

[removing this comment to a new thread]

[vwhite]

So it doesn't look like there is a straight forward solution to this querk in Excel but there is a workaround

https://www.excelforum.com/excel-pro...rd-module.html

Essentially it's a way to use VBA to toggle the conditional formatting on/off by commenting out the Has-Formula module

That way you can you turn it off before the file is saved ensuring Open_Workbook() events fires when the workbook is opened Huzzah

Thank you everyone for all you help
Once again proving nothing is impossible!