Saved = True not working in BeforeClose due to charts updating

kjehel

New Member
Joined
Sep 12, 2012
Messages
4
Hi. I have a seemingly strange problem with a macro enabled workbook. In the BeforeClose sub i force the workbook to save with
Code:
Me.Save
However, when I try to close the workbook Excel always shows the standard Save dialogue. I've doubled checked that
Code:
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?
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,770
Messages
5,470,687
Members
406,717
Latest member
Harsha Maskara

This Week's Hot Topics

Top