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?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top