Why am I prompted to save?

[Flintstone]

Why is it when closing certain files that I’m prompted to save when I
haven’t changed a thing?

This question was posted earlier about a month ago from someone else
and the response didn’t explain anything.

I understand why certain functions such as TODAY() and NOW() would
require the save dialog to pop up on closing but why INDIRECT, OFFSET
or CELL ect., what’s happening with these functions? I don’t get it.

Matt

 

[Alan]

The Save prompt is only triggered if something is changed.
This can be if there are macro's in the sheet, if there are links to other
sheets (and if the warning to update links is disabled in 'Options' so you
don't see the link updating), or if you have =TODAY() or =NOW() in a cell
somewhere.
Can't really think of anything else but I daresay someone else will,
INDIRECT, OFFSET etc functions won't trigger the prompt,
Regards,
Alan.

 

[Flintstone]

Thanks Alan

 

[Alan]

Did you cure it? If so what was it?
Regards,
Alan.

 

[Ken Wright]

Actually all the following volatile functions will cause Excel to prompt a
save:-

AREAS()
INDEX()
OFFSET()
CELL()
INDIRECT()
ROWS()
COLUMNS()
NOW()
TODAY()
RAND()

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Alan]

Hi Ken,
I don't understand. I use a file that contains OFFSET, INDIRECT and COLUMNS
and it doesn't prompt me to save unless I change something. I know you know
more about Excel than I do, but can you please explain how these functions
trigger the save prompt if they are not referring to another workbook??
Thanks,
Alan.

 

[Ken Wright]

I just opened a blank file, and in cell B1 on sheet 1 put the following
formula:-

=INDIRECT(A1)

and in A1 just had the Text 'A1'

I save, close, reopen and then hit close again - I get prompted to save.

Same with all the other functions and no links to any other files.

Just did those exact steps in both XL 2003 and XP and same in each case.

You should get no different results to that.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Max]

.. I save, close, reopen and then hit close again - I get prompted to

save.

Perhaps it's due to the calc mode of the Excel session (set to Manual ?)

If I open a book with INDIRECT from within Excel
(where Excel is already opened, and with calc mode set to: Manual)
then I can just close the book again w/o getting any prompt to save.

But if the Excel session's calc mode is "pre-set" to Auto,
then I'd get the prompt to save
even though I haven't touched anything in the book

---

 

[Dana DeLouis]

An interesting article...

XL: "Save Changes in <Bookname>" Prompt Even If No Changes Are Made

http://support.microsoft.com/kb/274500/en-us

 

[Max]

Probably, like me, Alan runs a lot of books
with the calc mode set intentionally to Manual,
that's why it didn't prompt for him <g>

---

 

[Alan]

Hi Ken,
You're right. The file I'm using doesn't have calculation set to manual by
VB code, but after looking at it carefully the BeforeSave event has been
modified quite a bit to only give the 'save' prompt under certain
conditions,
We live and learn!
Regards and Thanks,
Alan.

 

[Ken Wright]

Think you nailed it Max

Regards
Ken...............

 

[Ken Wright]

Hi Dana. Cheers for that link, hadn't seen that one.

Regards
Ken..............

An interesting article...

XL: "Save Changes in <Bookname>" Prompt Even If No Changes Are Made

http://support.microsoft.com/kb/274500/en-us

 

[Max]

If the comments are for my consumption,
the name's Max, Alan <g>

 

[Max]

Cheers, Ken !

 

[Alan]

Hi Max,
I do apologise, no offence intended. The code in the file I'm using returns
'This Workbook.Saved = True' unless certain conditions are met, as I said
we live and learn,
Regards,
Alan.