Saved = True not working in BeforeClose due to charts updating

[kjehel]

Hi. I have a seemingly strange problem with a macro enabled workbook. In the BeforeClose sub i force the workbook to save with

Me.Save

However, when I try to close the workbook Excel always shows the standard Save dialogue. I've doubled checked that

Me.Saved = True

when running BeforeClose sub, so the workbook definitely gets saved.

After an extensive search, I've narrowed the problem down to a sheet which contains charts. The data source for the charts is another sheet where I heavily rely on the INDIRECT function. It seems like Excel does a refresh of the charts after the workbook is saved. So if I do a copy & paste values in this sheet instead, Excel will close the book as expected without asking if I want to save the book.

I could hack this with some subs copying the formulas temporarily to another sheet and then pasting values, but that's not exactly elegant.

Any suggestions?

 

[Redwolfx]

The indirect function is a volatile function, it tends to continuously update with every change to the spreadsheet. you could try to set thisworkbook.saved=true in the before close to see if that works. if not the copy/paste may be one of very few options available.