Do You Want to Save the Changes You Made to

[Wayne]

Frequently I open a spreadsheet just to print it or to find
information, but don't make any chnages. When I close the
spreadsheet, many will simply close. However, some post the message,
"Do you want to save the changes you made to" [name of the
spreadsheet].

How do I eliminate getting this annoying message when no changes have
been made to the spreadsheet?

Note that I've made sure the "read only recommended" box in the save
as menu is not checked.

I'm using Microsoft Excel X for Mac Service Release 1. I have a dual
1.25GHz PowerPC G4 and 1GB DDR SDRAM.

Wayne

 

[JE McGimpsey]

Couple of things can make a workbook "dirty" such that XL will think a
change has been made.

1) Volatile functions: NOW(),TODAY(),INDEX(),OFFSET(),RAND(),INDIRECT()
(there's at least one that I'm forgetting). Volatile functions calculate
whenever the sheet calculates (which if Calculation mode is Automatic,
includes when the workbook opens).

2) If you have your preferences set to calculate before print, any
change will cause XL to think your workbook needs to be saved. These
include headers/footers that contain the date and time.

3) Event macros - if you have event macros in your workbook, firing
that/those event(s) may cause a dirty workbook, depending on the code
that fires.

There's one way I know of to eliminate the messages. Put this code in
your ThisWorkbook code module (ctrl-click on the workbook title bar and
select View Code).

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub

This is fairly dangerous, though, because you won't get prompts if you
realoly have made unsaved changes.

 

[Wayne]

JE,

Thank you very much for your advice.

Following up on it I found none of the functions in my workbooks that
you call volatile.

However, I found the problem feature -- it is an image in the
spreadsheet made using the excel "camera". This is an image that
updates when the source cells of the image change. If I delete the
image, I no longer get inappropriate "Do you want to save the
changes you made to" messages.

Does the image make the workbook "dirty" or is there a good work
around?

Wayne

 

[JE McGimpsey]

Thank you very much for your advice.

Following up on it I found none of the functions in my workbooks that
you call volatile.

However, I found the problem feature -- it is an image in the
spreadsheet made using the excel "camera". This is an image that
updates when the source cells of the image change. If I delete the
image, I no longer get inappropriate "Do you want to save the
changes you made to" messages.

Does the image make the workbook "dirty" or is there a good work
around?

Yes - the image updates on open and so makes XL think the workbook needs
to be saved.

The only workaround is what I posted previously - a workbook event macro
to tell XL that the file has been saved. Again, that has a downside of
not prompting you if you have legitimate changes that need to be saved.