Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Saving Non-Existent Changes.
Written by Allen Wyatt (last updated July 14, 2018)
This tip applies to Excel 97, 2000, 2002, and 2003
You probably had this happen to you: You open a workbook, look around at some of the worksheets, and then close the workbook. As part of closing, Excel asks you if you want to save your changes, yet you didn't make any changes—you only looked around. What gives?
Internally, Excel maintains what is commonly called a "dirty flag." This flag gets set whenever you do some sort of change to a workbook. Whenever you save the workbook, the flag is cleared. If the flag is set when you close the workbook, Excel asks if you want to save the workbook.
The dirty flag can obviously get set if you make some explicit change to a workbook, such as editing a cell or modifying the structure of the workbook in some way. However, it can also get set even if you don't do anything explicit. Sometimes, Excel does something that affects the contents of the workbook just by virtue of the fact you opened it. This sets the dirty flag and thus triggers the request about saving.
Two big culprits in making such automatic changes are the TODAY and NOW worksheet functions. These return the system date and the system time, respectively. When you first open a workbook, they are updated in the normal course of recalculating. Since they represent a change, Excel sets the dirty flag.
The dirty flag can also be set automatically if your workbook includes links to data on other worksheets. Excel retrieves the data, which represents a change to the workbook you just opened. Excel doesn't set the dirty flag if you simply navigate around the workbook, doing things like selecting cells or changing to a different worksheet.
One way you can get around the problem is to, of course, remove whatever is causing changes in your workbook. For most people, this just isn't practical. You can also add an automatic macro that will run just before the workbook closes, such as the following, which should be part of the ThisWorkbook object:
Please Note:
This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Saved = True
End Sub
This macro does nothing more than clear the dirty flag (the Saved property). While this approach will work, there is a huge risk inherent in using it. With the macro in place, Excel will never ask you if you want to save changes upon exiting, even if legitimate changes were done to the workbook. Thus, you would need to remember to explicitly save anything in the workbook whenever you make changes. If you don't, you may loose some of your work.
A variation on this approach—one that is less unforgiving of forgotten changes—is to actually make the macro part of the Workbook_Open procedure for the ThisWorkbook object:
Private Sub Workbook_Open()
ActiveWorkbook.Saved = True
End Sub
Now, Excel opens the workbook, recalculates (including making changes based on functions such as TODAY and NOW), and then clears the dirty flag. If you close right away, you aren't asked if you want to save your changes. You will be asked if you want to save changes, however, if you make changes after this macro has run—in other words, after the worksheet was fully opened.
Besides automatically recalculating functions that set the dirty flag, it is also possible that your workbook contains a macro or two that automatically run when you open it. If the macro is making some sort of change in the workbook, then it will naturally set the dirty flag. You can check out the VBA Editor to see if this is the case.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2081) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Saving Non-Existent Changes.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Need to cancel a command you've already started? It is as easy as pressing a single keystroke.
Discover MoreDialog boxes are designed to be limited in scope, so that they cover only a portion of your screen. What if a dialog box ...
Discover MoreWhen working with large numbers, you may need a way to quickly divide a range of those numbers by a specific value. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2022-03-14 15:41:45
Boris Pretzel
Hi,
I have a query on the workbook saved status that has been bugging me for a while.
I have a fairly intricate workbook that undertakes complex analyses on data. The workbook has a Workbook_Open and an Auto_Open macro, the last instruction on each being "ThisWorkbook.Saved=True). (I know it is superfluous to include that in the workbook-_open macro as the Auto_Open macro runs last but the command has insignificant overhead for me so...). Auto_Open checks whether Workbook_Open has run and, if not, duplicates much of the functionality of the Workbook_Open macro.
However, typically, after the file is open and everything is ready, ThisWorkbook.Saved returns false and I am prompted to save changes. This seems typically to happen if:
1/ it is the first time the file is opened (and I am prompted to to enable disabled components - ie allow macros)
2/ The file is opened from a network (or substituted) drive (typically for me as I keep my files on a substituted directory path which in windows 11 is treated as a network path).
Interestingly, when I get the more tradition dialogue box "Microsoft Office has identified a potential security concern..." with the option to Enable Macros or Disable Macros, or if I open the file from a non-substituted local path when I have previously opened that file and enabled macros / disabled active content, once open, the ThisWorkbook.Saved status remains True and I am not prompted to save changes on exit.
I am not entirely sure why sometimes I get the warning that active parts of the workbook have been disabled (“SECURITY WARNING Some active content has been disabled. Click for more details. [Enable Content}”), with the invitation to enable them, and sometimes I get the more traditional enable macros message box but, more importantly, I do not understand what Excel is doing behind the scenes that causes the Saved status to change to false having been set to True at the end of the Auto_Open macro. As I say, this is happening after the completion of my startup macros. As it does not happen when I do not get the disabled elements warning (ie when the file is treated as trusted), I do not believe it can be any of the internal contents of the workbook and is something imposed by the application. I would be keen to find out what this is and either suppress the behaviour or issue the ThisWorkbook.Saved instruction after this interaction. As I am sharing the application with colleagues, setting it to a trusted document would not be an appropriate solution.
Do you have any idea of what is causing the behaviour in Excel or how I can influence or change it? I am using Excel 2019 at the moment (but had this issue with versions from 2013 and up on office 365) and am running in a Windows 11 Pro environment (previously Windows 10 enterprise). I hope that the description of the issue is sufficiently clear!
Many thanks for any help / pointers.
2020-01-31 11:58:55
Microsoft continues to drop my jaw to the floor with bugs like this. Why can they not understand that messages like this confound and confuse the user? There are different things as they can do, the least of which is to "CLEARLY STATE THAT THE USER MADE NO CHANGE" but that a formula is the cause of this SAVE PROMPT. How many times have you asked, "Did I make a change? Was it an accident? Was it a deliberate change in a spreadsheet that has been open for a few hours/days?
This is as bad as prompting to save an old format into a new one...as though the software is 'tired' of converting the spreadsheet 'on-the-fly'.
Why does this matter to us? Because TIME-DATE STAMPS are changed on old accounting files. They are perhaps the only indicator of when the file was created/last saved...and Excel wipes this crucial information out at the drop-of-a-hat.
Why are programs written by people that apparently do not use them on a daily basis like the rest of us?
2018-07-30 22:30:30
I tweaked a few things in a worksheet of mine that has been doing this for years and in fact solved the problem finally. However I often need to go in to the file and click in a cell, copy the text, and paste it in a completely unrelated document. (There are no links to other documents, formulas, or anything like that. It's a pretty simple worksheet, albeit long.) Any idea why this continues to happen? Does choosing to click in a cell and copy the text trigger the save prompt?
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments