Hi,
Under a specific condition, the following code falls into and endless save loop in excel 2007...this does not happen in excel 2003...
To make things fail:
1. Workbook with code is open with another random workbook open at the same time.
2. The workbook with code (ThisWorkbook) happens to be the ActiveWorkbook.
3. Click the red X to close excel. When prompted about saving answer yes....thus the loop begins..as long as you continue to click yes, the workbook will be saved over and over again.
In 2003, when you clicked the red X and said yes to save, the BeforeSave event would fire, save the workbook, and set the .saved to true..then it would exit the BeforeSave Event, excel would see that the .saved value of the workbook was true, and the workbook would close.
Not true in 2007...when you close, you get the dialog box about saving, and if you say yes the BeforeSave event still fires, but for some reason after exiting the BeforeSave routine excel does not see the .saved of the workbook as true, so it asks again about saving.
This is driving me NUTS!!!!
Anyone know why this is not working?
The BeforeSave Code:
Thanks...i hope that made sense
Jason
Under a specific condition, the following code falls into and endless save loop in excel 2007...this does not happen in excel 2003...
To make things fail:
1. Workbook with code is open with another random workbook open at the same time.
2. The workbook with code (ThisWorkbook) happens to be the ActiveWorkbook.
3. Click the red X to close excel. When prompted about saving answer yes....thus the loop begins..as long as you continue to click yes, the workbook will be saved over and over again.
In 2003, when you clicked the red X and said yes to save, the BeforeSave event would fire, save the workbook, and set the .saved to true..then it would exit the BeforeSave Event, excel would see that the .saved value of the workbook was true, and the workbook would close.
Not true in 2007...when you close, you get the dialog box about saving, and if you say yes the BeforeSave event still fires, but for some reason after exiting the BeforeSave routine excel does not see the .saved of the workbook as true, so it asks again about saving.
This is driving me NUTS!!!!
Anyone know why this is not working?
The BeforeSave Code:
Code:
Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim aWs As Worksheet, newFname As String
On Error GoTo Err
'Turn off events to prevent unwanted loops
Application.EnableEvents = False
'Turn off screen flashing
Application.ScreenUpdating = False
'Record active worksheet
Set aWs = ActiveSheet
'Save workbook directly or prompt for saveas filename
If SaveAsUI = True Then
newFname = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")
If newFname <> "False" Then
ThisWorkbook.SaveAs Filename:=newFname
Cancel = True
ThisWorkbook.Saved = True
Else
Cancel = True
End If
Else
ThisWorkbook.Save
Cancel = True
End If
'Restore file to where user was
aWs.Activate
'Restore screen updates
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
Err:
MsgBox ("Failure")
aWs.Activate
Cancel = True
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Thanks...i hope that made sense
Jason