Excel 2003 - Workbook_Activate bug for ActiveSheet

Question

Context:
Excel 2003, Windows 7 Professional SP1

1. Working on workbook A, which contains custom functions.
2. Workbook B is opened or edited

3. Back to workbook A, which Workbook_Activate sub is the following:

   Private Sub Workbook_Activate
       ActiveSheet.Calculate
   End Sub
   

4. This procedure should force the calculation of custom functions of workbook A, which cells display #VALUE during the edition of workbook B.

Below are the bugs I get on my computer, I hope you can help me with them!

• When the workbook (not the whole Excel window) IS NOT in state ActiveWindow.WindowState = xlNormal (i.e. it is full screen or minimized)

OR:

• When the workbook IS in state ActiveWindow.WindowState = xlNormal and the workbook has a width > 725 pixels (!)

THEN:

• The function Workbook_Activate is fired all the way (good thing).
• The function ActiveSheet.Calculate is not fired (or without effect).
• Breakpoints don't stop the code.
• You can't run any code that edits cells, or run Application.CalculateFull, without getting a display bug such as entire ranges where the text or border vanish until the ranges have been selected.

PROBLEM SOLVING:

In procedure Workbook_Activate, if we add ActiveWindow.WindowState = xlNormal before ActiveSheet.Calculate:

When the workbook IS NOT in state ActiveWindow.WindowState = xlNormal and the Excel window IS maximized

• ONLY in this case, the bugs remain. It's the only case where function ActiveSheet.Calculate is not fired. ActiveWindowmethods don't fire either.

Obs.: Application.EnableEvents = False doesn't change anything. (It's probably not even triggered.)

Thank you!

Answer 1

I found a simple solution to avoid the bug.

Private Sub Workbook_Deactivate()
    Application.Calculation = xlCalculationManual
End Sub

Private Sub Workbook_Activate()
    Application.Calculation = xlCalculationAutomatic
End Sub

[Edit]
... But it was not acceptable, because the calculation was set to "Manual" by default on any other opened workbook.

The best solution is here: Excel 2003 - Display bug with conditional formatting