Endless Save loop in 2007

[JasonC71]

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:

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

 

[JasonC71]

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.

...O.K...that statement is all correct except the part about excel not "seeing" .saved as true..that particular part is what i assume is happening.

 

[random_noise]

Not sure why you need to 'activate' the current sheet again?

Perhaps because you are doing this after everything else it thinks you've made some more chages and so is resetting thisworkbook.saved to false??

Just a thought

 

[xenou]

1)
What's the strategy with the SaveAsUI here? Does your getsaveasfilename statement run after the user has chosen another saveas file name in the Excel SaveAs dialog box? I'm going out on a limb and guessing that if SaveAsUI is false, you can just let the normal save action occur - no need to interfere and the file is saved with its current name/path. I could be wrong on that.

2)
I don't see how this would change anything but maybe a slimmed down version would help pinpoint the problem...? Probably not but anyway...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim newFname As String

On Error GoTo Err
Application.EnableEvents = False

    'Save workbook directly or prompt for saveas filename
    If SaveAsUI = True Then
    
        newFname = Application.GetSaveAsFilename( _
                    fileFilter:="Excel Files (*.xls), *.xls")
        If newFname <> "False" Then
            Cancel = True
            ThisWorkbook.SaveAs Filename:=newFname
        Else
            'No filename was given; cancel save
            Cancel = True
        End If
    
    End If
                
         
MyExitSub:
Application.EnableEvents = True
Exit Sub

Err: MsgBox "Error " & Err.Number & ": " & Err.Description
Cancel = True
Resume MyExitSub
End Sub

 

[JasonC71]

Thanks for the replys..I will take a look and play with things later today...

Random noise..the activate current worksheet is in there because in the "full" code, there are a couple of calls to other Subs that hide and unhide some sheets before close. I took those calls out in the posted code cause I was trying to Isolate the problem...I did not matter.

Mr. Barnes..the saveas..yes, the user selects/inputs a file name and the the get statment then runs. As to letting things happen otherwise, I can't as I need the spreadsheet saved with specific sheets visible and others hidden...that said I will try things as you have written them and see if that helps me find the difference in excel 2007....It's annoying in that the code works perfectly in 2003....something changed and I can't figure out what.

Thanks for your time,

Jason

 

[JasonC71]

Hi all,

I'm bumping this thread because I STILL have not figured this out...I can't get the code to play nice in 2003 and 2007 versions of excel. The code itself is a part of a workbook that is in about 200 retail units..the issue is currently not that big as 99% of the stores have excel 2003..but It would be nice to know that things work in 2007. For clarity sake, I'm going to post the complete code. It's a "force macros enable" routine...I HAVE to use it because the end users are not exactly excel users..thus I have to take control of things like copy/paste. I have tried adding a before close event..no dice...I have tried moving the line Thisworkbook.saved = true to the last line of the before save event..no dice...in excel 2007 on close I get and endless save loop....any help or advice would be greatly appricated.

The Complete Code:

Option Explicit
 
Const WelcomePage = "Macros"
 
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
        'Hide all sheets
         Call HideAllSheets
            '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
                    Call ShowAllSheets
                    Cancel = True
                    ThisWorkbook.Saved = True
                Else
                    Cancel = True
                    Call ShowAllSheets
                End If
            Else
                ThisWorkbook.Save
                Cancel = True
                ThisWorkbook.Saved = True
                Call ShowAllSheets
            End If
 
        'Restore file to where user was
         aWs.Activate
        'Restore screen updates
         Application.ScreenUpdating = True
         Application.EnableEvents = True
Exit Sub
Err:
    Call ShowAllSheets
    aWs.Activate
    Cancel = True
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 
End Sub

Private Sub Workbook_Open()
     'Unhide all worksheets
    Application.ScreenUpdating = False
    Call ShowAllSheets
    Application.ScreenUpdating = True
End Sub

Private Sub HideAllSheets()
     'Hide all worksheets except the macro welcome page
    Dim ws As Worksheet
    ThisWorkbook.Unprotect ("J")
    Worksheets(WelcomePage).Visible = xlSheetVisible
 
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVeryHidden
    Next ws
 
    ThisWorkbook.Protect ("J")
 
 
End Sub

Private Sub ShowAllSheets()
     'Show all worksheets except the macro welcome page
 
    Dim ws As Worksheet
    ThisWorkbook.Unprotect ("J")
 
    For Each ws In ThisWorkbook.Worksheets
        If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
    Next ws
    Worksheets(WelcomePage).Visible = xlSheetVeryHidden
    Worksheets("P AND L DATA").Visible = xlSheetVeryHidden
    Worksheets("DAILY SALES DATA").Visible = xlSheetVeryHidden
    Worksheets("PROJ CALC").Visible = xlSheetVeryHidden
    Worksheets("PROJ TEST").Visible = xlSheetVeryHidden
    Worksheets("DAILY SALES").Visible = xlSheetVisible
    Worksheets("MONTHLY BUDGET").Visible = xlSheetVeryHidden
    Worksheets("PS2").Visible = xlSheetVeryHidden
    Worksheets("HELLO").Activate
    Worksheets("HELLO").Range("C2").Select
    ThisWorkbook.Protect ("J")
 
End Sub

Thanks again,

Jason