ThisWorkbook.Saved does not work!

[Niklas]

Hi
I have a workbook with 18 sheets and most of the VBA used
by the sheets is refereces to XLA files on the network.
Before the application is closed I want to inform the user
that the workbook is not saved if the user had forgotten
to save the workbook.

When I open the workbook the property ThisWorkbook.Saved =
True, but as soon as I close the Workbook (1 second after
opening) and catches the event Workbook_BeforeClose the
property have been set to False...why??? I do not have any
code in my project setting the property to False...does
anyone know what to do?

Best regards
/Niklas

 

[Pete McCosh]

Niklas,

from VBA help:
Saved Property =
True if no changes have been made to the specified
workbook since it was last saved. Read/write Boolean

Therefore, when you open your file and allow the links to
update, a change has been made which sets the Saved
property to False.

In other words, you should be checking for .saved=false if
you want to check the user hasn't forgotten.

Pete

 

[Niklas]

Hi

I do not have any links. When I wrote refereces I meant
that I have used Tools->Referencess... in the editor. The
property Saved is True after I have opened my file. I
catch the event Workbook_BeforeClose after I have done
File->Close and in this event the Saved property is False.
What made the property to be False? I only did an "Close"!
Best regards
/Niklas

My question is: how can the workbook

 

[Tim Zych]

There are other ways this can occur, such as with Volatile formulas that
recalculate frequently:

XL: "Save Changes in <Bookname>" Prompt Even If No Changes Are Made
http://support.microsoft.com/default.aspx?scid=kb;EN-US;274500

So if you have volatile formulas, the saved property will be false
immediately after opening because of recalculation, even if you don't touch
the workbook.

 

[George Nicholson]

Niklas:
workbook

...and as soon as this occurs, the Saved property is set to False because you
have made a change to the Workbook.

Not true. You also set a button caption.

The following code demonstrates the sequence:

Private Sub Workbook_Open()

Sheets(1).Range("A1") = ThisWorkbook.Saved
Sheets(1).Range("A2") = ThisWorkbook.Saved
ThisWorkbook.Saved = True

' 1st line returns TRUE: no changes have been made yet (just like your
button).

' 2nd line returns FALSE: the first line made a change by writing a
value to a cell.
' Setting a button caption would have the same effect.

' POSSIBLE REMEDY:
'3rd line "cancels" the effect of the previous changes on the Saved
property, but does not cancel the actual changes.

' So, if you exit the workbook now, you will not get the "Save
Changes?" prompt,
' *but* the changes made by lines 1 & 2 will not have been saved.
However, as long as those changes were
' generated by code in your Opening routines, this is probably
acceptable. This is why the property is Read/Write instead of Read Only.

End Sub


Hope this helps,

 

[Niklas]

Hi
I'm sorry that I was not clear enought. The button only
executed "msgbox Thisbook.Saved" so I did not change any
Caption. The only thing I do is File->Exit...and the
moment before the property Thisbook.Saved was True.
I have found a Workaround and that is that I execute the
line "ThisWorkbook.Save" in the event Workbook_Open(), but
I do not like this type of workaround coding!
Best regards
/Niklas