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

mickey wrote:
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