Problem with "ThisWorkbook.Saved" property.

[mickey]

Objective: I wanted the workbook to simply ignore certain changes made prior
to closing.

The change, to be ignored, occurs during a CommandButton_Click event. The
code stores the "State" of the "Saved" property. On exiting the "Click"
routine the "Saved" property is restored to "True" if it was originally
"True" when entering the "Click" event routine (In case some activity other
then the "Click" event changed the workbook and needs to be saved).

Problem: Although the "Click" event is the only thing causing a change to
the workbook, and indeed the state of the "Saved" property prior to saving is
confirmed as "True", when the "X" is clicked to close the workbook, a MsgBox
message confirms that something changes the "Saved" property to "False"
causing the normal Excel message save message to be issued.

I've included sample code below that illustrates the problem:

Note: A CommandButton must be inserted somewhere on the workbook.

In Module 1:

Sub SubOn()
Sheets("Sheet1").CM1.Caption = "Off": MsgBox "CM1.Caption = " &
Sheets("Sheet1").CM1.Caption
End Sub

Sub SubOff()
Sheets("Sheet1").CM1.Caption = "On": MsgBox "CM1.Caption = " &
Sheets("Sheet1").CM1.Caption
End Sub

In Sheet1:

Private Sub CM1_Click()
Dim State As Boolean

State = ThisWorkbook.Saved: MsgBox "State is " & State

If CM1.Caption = "On" Then
SubOn
Else
SubOff
End If

If State Then ThisWorkbook.Saved = State

End Sub


In Workbook_BeforeClose:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

MsgBox "ThisWorkbook.Saved is " & ThisWorkbook.Saved

End Sub

If one alternately clicks the CommandButton, the state of the "Saved"
property is displayed and then the caption is alternated between "On" and
"Off". Note that even though the caption is changed, which causes the
"Saved" property to change to "False" the "True" state is resorted on click
exit. This is confirmed the next time the CommandButton is clicked, again
displaying the state of the "Saved" property when entering the "Click"
routine. However, simply closing the workbook, by clicking the "X", will
show that the "Saved" property has changed to "False" (MsgBox in
"BeforeClose" event).

Does anyone know what could be causing "ThisWorkbook.Saved" to change from
"True" to "False" just by closing the workbook?

Thanks

 

[John Coleman]

Hi

Looks like you have found a bug (though I'm sure that Microsoft spin
control could call it a "design decision"). It is interesting that if
you change the click event to

Private Sub CM1_Click()
Dim State As Boolean

State = ThisWorkbook.Saved: MsgBox "State is " & State
Range("A1").Value = Range("A1").Value + 1
ThisWorkbook.Saved = State

End Sub

(Note how I changed the last line - it is fully equivalent to "If State
Then ThisWorkbook.Saved = State" but a little more readable)

The problem doesn't appear. Note that a command button is an embedded
active X control - so maybe when the workbook is closed it looks both
for changes in the workbook per se and any embedded object and if it
finds a change in either it sets saved to false.

A work around would be to delete Dim State as Boolean and put Public
State as Boolean at the top of Module 1 then put ThisWorkbook.Saved =
State in the BeforeClose event.

Happy New Years

-John Coleman

 

[John Coleman]

Sorry, the work around doesn't work! (I thought I had an example where
a false False in the before Close was successfully changed to True -
but I can't reproduce it). Interestingly, even

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If State Then
ThisWorkbook.Saved = True
Application.DisplayAlerts = False
ThisWorkbook.Close
End If

End Sub

is defeated - DisplayAlerts is turned back on and Saved is turned back
off. Mysterious. Just what are you changing in your code? Is it an
embedded object?

It looks like you might have to do something extreme like create a
backup copy of the workbook at the start of the macro and restore from
the backup if saved = true at the end of the macro.

-John Coleman

 

[mickey]

Thanks for your response.

In the actual workbook, clicking on the command button inserted some
temporary data into a worksheet, clicking again on the command button removes
the data. I did not want the user to save the worksheet with the temporary
data. Originally, I had a routine located in the "BeforeClose" event to
either delete the temporary data, or to simply advise the user to do so
before saving. Alternatively, I thought of simply marking the sheet as
already saved, causing the application to close without saving: that's when I
ran into the current problem.

 

[mickey]

Hi John,

You were correct regarding the CommandButton Caption. If I eliminate the
change the "Saved" property works correctly.

This is probably related to another problem I discovered with the
CommandButton during a save. [Search posting for "CommandButton Anomily"] On
closing I had a routine in the "BeforeClose" event remove the temporary
information in the worksheet, and change the caption back to it's initial
state. Through various MsgBox functions, I confirmed that the caption had
indeed changed back, but during the time the standard Excel "Save"
confirmation message was displayed the Caption reverted back, as if I hadn't
changed it. However, on reloading the application, the caption was correct.
So there is something about the CommandButton that Excel does not handle
correctly on closing.

 

[mickey]

John,

If your still following this thread, and are interested, I've come-up with a
solution.

I created a UDF that stores the "Saved" state, then the "BeforeClosed" event
sets the "Saved" property to the stored "Saved" state. This overrides the
"False" caused by the CommandButton caption change, while still preserving
any other change that may affect the "Saved" property.

Cheers

 

[John Coleman]

Mickey,

I'm glad that you found a work-around.

Have a good day

-John