Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Thanked 0 Times in 0 Posts

    Prompt to save non-existent changes (Excel97 SR2)

    The following was taken from "ExcelTips", an email service offering tips for MS Excel. I have a problem with always being prompted to save changes to a particular workbook (containing 17 worksheets) even though I might only open the workbook, and immediately close it without doing anything at all to the the workbook. The workbook contains a worksheet that used the TODAY function.

    In the following tip, I used the VBA editor for the workbook to copy the second of the macros (the workbook_open macro) that is
    Private Sub Workbook_Open()
    ActiveWorkbook.Saved = True
    End Sub
    Nevertheless, Excel still always prompts me to save changes, even though no changes were made. Any suggestions?


    Saving Non-Existent Changes
    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

    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.

    The two biggest 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.

    One way you can get around this is to, of course, remove any TODAY or
    NOW functions from your workbook. For most people, this 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:

    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.

    (Thanks to Steve Aprahamian, David Colpo, Maureen Reardon, Yosef
    Kedem, George Stanley, Peter Cloutier, Pete Bayley, and Gavin Tameris
    for contributing to this tip.)


    Thanks for any help offerred

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Prompt to save non-existent changes (Excel97 SR2)

    I would just use the before close event macro:

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Saved = True
    End Sub</pre>

    I can only imagine that you are doing something to cause a calculation after the workbook is opened and the filesave flag has been set. if it is in The before close event will excel will NEVER prompt you to save EVEN if you have made changes!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts