Want Excel to prompt for save *only* once manually changed

[JesOakley]

I have some spreadsheets which I mainly use for reference, but when I exit I'm prompted to save whether I do anything or not. Yes; there is some macro code in there which is undoubtedly doing this and which I don't really want to change.

What I would really like to do is reset the 'dirty' flag to 'clean' once the spreadsheet has been presented to me, so that any changes I make manually set it back to dirty, but if I simply look at the code then I can quit without being prompted.

Is there a suitable event for this? I googled for events and descriptions of when they triggered but didn't find anything.

Thanks in advance.
Jes

 

[combo]

At the end of macro add:
[tt]ChangedWorkbookReference.Saved=True[/tt]
You may need to test the "Saved" value at the beginning of macro in case of situation when the workbook was changed manually and next by macro.

combo

 

[JesOakley]

Thanks for the reply Combo,
I was just about to implement your suggestion when I realised that I hadn't thought it through. My macro is invoked manually, and therefore probably isn't responsible for setting the dirty flag as my spreadsheet asks me to save it whether or not the macro is invoked. I use the the TODAY function in the spreadsheet and I suspect that this is my issue. the question now is, where do I put the line of code you supplied so that it is after the TODAY recalc-on-open but before I make any manual changes?
Here's hoping you can help further.

 

[combo]

You could use Workbook_SheetChange and BeforeClose event procedurs. In the first one, depending on the action flag (manual, code) and previous state (needs/does not need to be saved), set the new state, in the second set the "Saved=True" if you don't need the message.

combo

 

[madonnac]

Excel has "volatile" functions
Using these functions will always result in excel prompting you to save changes, even if you just open the file

RAND(), NOW(), TODAY(), OFFSET(), CELL(), INDIRECT(), INFO()

This site has a more detailed discussion:

http://www.decisionmodels.com/calcsecretsi.htm