'Do You Want to Save' message (Excel97/2000)

[jujuraf]

I have a workbook that never contained macros (and still doesn't). Now I notice that when I open and immediately close it (no changes were made to any cell), I get the message "Do you want to save the changes you made to 'filename.xls'." Why does this message come up when nothing was changed? It's got to be something messed up internally but since there' s code (or add-ins) what could cause it?

How can I get rid of it?

Thnx, Deb <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

[Andrew Cronnolly]

Deb,

Would you have any formulas or links that are updaiting automatically. Formulas using TODAY() or NOW() would cause an update.

Andrew C

[jujuraf]

Good thought there.. but no, I don't use those at all. I checked all formulas and only have 5 types (sum, round, if, ceil, floor) + regular cell references. Something has happened to this file because it never used to display this message if I just open/close the file. I guess it's yet another fault of Excel's poor garbage collection or internal crumminess. I'll go ahead and copy all the sheets to a new workbook. That should get rid of the message but it still doesn't explain what got messed up.

Thnx, Deb <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

[pieterse]

No (hidden) names with those formula's?

[jujuraf]

Yes, the model does use named vars to reference cells. A few formulas do reference these named cells. How would using named vars affect whether or not Excel thinks the model has changed? Also this wouldn't explain the fact that the model never used to ask "do you want to save" when it was just opened and immediately closed.

Hmmm, so what are you thinking about named vars that might point to to what's going on? I use them a lot.

Deb <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

[jujuraf]

Thanks again. No formulas are using things like Now() or Today() as everything in the model is financial-type data (quarterly totals). There are no UDFs either as a matter of fact there is no code (VBA), no macros at all. I tried the FindLink program but it didn't come up with anything that I would recognize as causing a change. I really think now it's just a quirk in Excel where it got itself twisted internally and I need to rebuild the application.

Oh, well, time to rebuild *yet again*

Thnx, Deb <img src=/S/hairy.gif border=0 alt=hairy width=15 height=15>

[pieterse]

Is it too large (or confidential) to attach to a msg here?

Other thought: Does it still do this when you first open XL without any add-ins:

Start, Run,

"C:Program FilesMicrosoft OfficeOfficeExcel.exe" /Automation

Quotes mandatory, Note you may have to change the path.

[danarbane]

I have had that problem with functions such as CELL("filename"). Just a thought.

Dan

[pieterse]

<<Prompts to save 'updates' caused by volatile formulae can be nailed with a macro, at the risk of being prompted about whether you want to allow macros to run, depending on how you've got Excel configured:
>>

Rather risky, since it also prevents the save question when you've made "real" changes...

[macropod]

This behaviour isn't restricted to time-dependant formulae like today() and now(). Two others that spring to mind are rand() and offset().

Prompts to save 'updates' caused by volatile formulae can be nailed with a macro, at the risk of being prompted about whether you want to allow macros to run, depending on how you've got Excel configured:

Sub Auto_Open ()
ThisWorkBook.Saved = True
End sub

[macropod]

Hi Jan,
That isn't how Excel 97 works - try it with the attached file. This does not give a prompt for a change caused by the volatile TODAY() formula, but does if the user makes a change. Maybe you were thinking of:

Application.DisplayAlerts = False

which would have that effect.

[pieterse]

Sorry for this, I misread your post, thinking the saved=false part was in an Auto_close macro.
Clever to have it in the auto_open macro.

[jujuraf]

Unfortunately I can't use any macros in this .xls file so can't use your suggestion. I checked again the formulas as no one uses rand() or offset(), I only use the 4-5 that I mentioned before. I think something changed internally since this model never used to have this problem and no formulas were changed (yeh, I know they always say "but I didn't change anything") *ha*

I decided to make a copy of the file and then one by one delete a sheet to see if the problem goes away (delete one sheet, save,exit, re-open). That way I can at least narrow it down to one (or more) sheets. I can't send the model as it contains proprietary information.

I appreciate your ideas though <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Deb

[jujuraf]

PROBLEM SOLVED!! <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

I deleted one sheet at a time until the problem went away then focused on that one sheet. It turned out that the combo box I had used from the Forms tool bar was causing the message to appear even if nothing was changed (at least nothing that I could see). I have 6 of these combo boxes on this one sheet and when replaced them with 6 list boxes, the message no longer appeared.

First I thought t was just one combo box, but it wasn't. So something about combo boxes internally causes something to change such that Excel prompts you at close. I tried it with the combo box in the Controls (ActiveX) tool bar and those worked fine (no erroneous 'do you want to save' msg).

Yippie, Deb <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>

[pieterse]

I just meant to ask whether there are any named formula's that contain function's like NOW, TODAY, etcetera.

I don't know what e.g. Application.Volatile causes to happen to UDF's regarding your problem. Got any?

Have you tried my flexfind utility to look for NOW or TODAY? Maybe even better: have FINDLINK from Bill Manville look for them, it will look inside Named ranges as well. Find it at http://www.bmsltd.ie/mvp

Edited Mar 13th 2004 to update link