Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook won't save
Hi All,
I have a worksheet with Customized BeforeSave and BeforeClose events. The Customized BeforeSave event allows me to let the user save the workbook and then output one of the sheets in xml format to a file with the same name as the workbook but with a .xml extension. The Customized BeforeClose event allows me to restore the toolbars to the state they were in when the spreadsheet opened. The problem is as follows. IF I open the spreadsheet, change the data and then close the spreadsheet, the message box appears saying "Do you want to save changes .....". I answer yes. Stepping through the code I find that the Me.Save statement in the BeforeClose event triggers the BeforeSave event. So far so good. Stepping through the BeforeSave event I get to the ThisWorkbook.Save statement. The debugger shows this statement being executed but the workbook does not save. Executing ThisWorkbook.Save in the Immediate window has no effect either. Allowing the code to continue running causes the workbook to close without any changes being saved. To reproduce this problem add the following code to the ThisWorbook module of a blank spreadsheet. Save the workbook and close it. Open the workbook, change some data somewhere and select close. Private Sub Workbook_BeforeClose(Cancel As Boolean) If Not Me.Saved Then Msg = "Do you want to save the changes you made to " Msg = Msg & Me.Name & "?" Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel) Select Case Ans Case vbYes Me.Save Me.Saved = True Case vbNo Me.Saved = True Case vbCancel Cancel = True Exit Sub End Select End If On Error Resume Next 'RestoreToolBars ThisWorkbook.Saved = True End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim sFile Application.EnableEvents = False Cancel = True On Error Resume Next If SaveAsUI Then Do Err.Clear sFile = Application.GetSaveAsFilename(ThisWorkbook.Name, "Excel Files (*.xls), *.xls") If Err.Number < 0 Then Err.Raise (Err.Number) GoTo Workbook_BeforeSave_Exit End If If sFile < False Then ThisWorkbook.SaveAs sFile Else GoTo Workbook_BeforeSave_Exit End If Loop Until Err.Number = 0 Else ThisWorkbook.Save End If ' Call SaveAgRaterAsXML(sFile) ThisWorkbook.Saved = True Workbook_BeforeSave_Exit: Application.EnableEvents = True ThisWorkbook.Saved = True Cancel = True End Sub Any help would be greatly appreciated. Regards Tony |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook won't save
Hi Tony,
Looks like you are cancelling out you action. It works if youchange the last (second last row) Cancel = True in the Private Sub Workbook_BeforeSave To Cancel = False Regards, Bondi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook won't save
Thanks Bondi,
That did the trick, but I'm not quite sure why that doesn't cause the workbook to be saved twice. I though that after executing the Thisworkbook.save statement in the BeforeSave event you would need to set Cancel = True to stop the workbook from being saved again. I would greatly appreciate if you could set me straight on this one. Regards Tony "Bondi" wrote in message oups.com... Hi Tony, Looks like you are cancelling out you action. It works if youchange the last (second last row) Cancel = True in the Private Sub Workbook_BeforeSave To Cancel = False Regards, Bondi |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Workbook won't save
Hi Toby,
I'm not that strong on the theory. I would like to help you get an answer thou, since it got me puzzled.. I hope someone outthere can help us:) Sorry to not straighten you out.. Regards, Bondi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shared workbook - to save or not to save | Excel Discussion (Misc queries) | |||
Select sheet tabs in workbook & save to separate workbook files | Excel Worksheet Functions | |||
Using interop.excel to open a workbook, the workbook doesn't ask to save changes. | Excel Programming | |||
Help on Workbook close and workbook save events | Excel Programming | |||
What commands do you use to name a workbook, save a workbook,open a workbook | Excel Programming |