Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with "ThisWorkbook.Saved" property.
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Document not saved" "error in loading DLL" | Excel Discussion (Misc queries) | |||
Solution "Your changes could not be saved" "The document may be read-only or encrypted" | Excel Discussion (Misc queries) | |||
"Invalid property" after "Clear Form" | Excel Programming | |||
Multiple "Range" with "Cells" property? | Excel Programming | |||
Multiple "Range" with "Cells" property? | Excel Programming |