Excel 2003 - Display bug with conditional formatting

Question

This is actually an improvement for this previous topic.

Context:
Excel 2003, Windows 7 Professional SP1.

• Working on workbook A, which contains a cell with conditional formatting calling a personal function. This function refers to a defined name of workbook A.
• Workbook B is opened or edited (not only activated). It can be any workbook.
• We activate back workbook A.

Problem:
The conditional formatting causes various bugs if workbook B's window overlaps workbook A's. In other words, when at some point workbook A gets behind workbook B.

Depending on the window state (whether Excel is in full screen or not, whether ActiveWindow.WindowState = xlNormal or xlMaximized, whether the window is narrow or wide ...), the bugs can be :

• Mainly: cells calling personal (volatile) functions that refer to defined names don't calculate when switching back to workbook A. This is a huge problem, as I need to force calculation with Ctrl+Alt+F9.

• Display bugs often occur, notably when workbooks A and B are floating next to each other, overlapping each other. Cell content vanish, ghosts cells of the ones with conditional formatting appear, the screen freezes, window border trails (W98 style) are displayed when trying to move around one of the workbooks ...

• In Workbook_Activateof workbook A, breakpoints don't stop the code (though debug.print and MsgBox work) and you can't use Application or ActiveWindow methods. Application.CalculateFull sometimes causes all the cells' content to disappear until they are selected (using Ctrl+A, for example).

How to solve or avoid this problem?

I will answer to myself below, if it can be of some interest for anyone!

Answer 1

Solution 1: Just don't use personal functions referring to defined cells names in conditional formatting formulas. This solution solves every problem, but it's not acceptable when you have to have complex conditional formatting.

Solution 2:

Bug sample
Bug sample (solved)

1. Create a named cell anywhere in workbook A, for example call it "CondFormat". Set its value to "TRUE".
   This will be the boolean driving the call of personal functions in your conditional formatting formulas.
2. Edit every conditional formatting this way:
   =MyFunctionWithCellNames($A1)
   becomes:
   =IF(CondFormat,MyFunctionWithCellNames($A1))
3. Automatically toggle the boolean when you activate or deactivate the workbook:
   Private Sub Workbook_Activate() [CondFormat] = True End Sub
Private Sub Workbook_Deactivate() ThisWorkbook.Names("CondFormat").RefersToRange = False End Sub

Note 1: sometimes, [CondFormat] isn't toggled when leaving the workbook. The ThisWorkbook.Names syntax makes it work all the time.

Note 2: we have to use one of the cells for the boolean CondFormat, because only macros can access to VBA global variables.

Note 3: I spent a lot of time figuring this out, I hope it will be useful for others. I think that most problems with conditional formatting can only be solved this way in Excel 2003, as Sheet.EnableFormatConditionsCalculation method only appeared in Excel 2007.