Excel 2007 formula behaving strangely

[Macca]

Workbook1 contains a macro that creates Workbook2 (xlsx fileformat = 51)which contains no macros.

When created, workbook2 contains worksheet "Labels" which holds data and no formulas

Workbook2 also contains worksheet "Results" which contains formulas.

On worksheet "Results", Range D2 can either be blank or have data.

On worksheet "Results" Range C7 is the following formula

=IF($D$2<>"",Labels!$I$5,"")

When data is entered in D2 and the focus moves to another cell, no text appears in C7.

However, if I click "Save", the text appears in C7.

If I then delete the data in D2, the text in C7 remains. But, if I click Save, it disappears.

The same thing happens with all the cells dependent upon data being present or absent.


Has anyone experienced this before? Does anyone know of a solution?


Workbook1 is a "clean" creation with the macro transferred from a testbed workbook, and the macro creates and populates worksheet "Labels" before it creates worksheet "Results".


I'm stumped!

 

[Luke M]

Sounds like you calculation setting is set to manual, calculate on save. With Workbook 1 open, check under Formulas - Calculation - Calculation Options. This should be set to Automatic.

This might have been caused accidentally by some other workbook, or there may be a line in the macro which is the culprit. If the latter, it would look like

[pre]

Application.Calculation = xlCalculationManual

[/pre]

 

[Macca]

Got it in one! I'd got the macro to close the created workbook before the reset lines for calculation and screen update - Doh!! Many thanks.

 

[Luke M]

No worries. Biggest reason I suspected the macro was because it's something I've done myself. =P