Why is my file not saving?

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
Hi MrExcel-land,

I have a file which needs the save functionality to be handled in a slightly strange way. To this end, I have written the following code:~

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
    If SaveAsUI = False Then
        If Right(ThisWorkbook.Path, 3) = "zip" Then
            Cancel = True
        Else
            Cancel = True
            Application.EnableEvents = False
            Me.Save '***********
            Call AfterSave
            Application.EnableEvents = True
        End If
    Else
        Cancel = True
        Application.EnableEvents = False
        Dim FileSaveFullPath As String
        Do
        FileSaveFullPath = Application.GetSaveAsFilename("08 PSE Daily Figures (New).xls", _
                , , "Please select a location to save the file")
        Loop Until FileSaveFullPath <> "False"
        ThisWorkbook.SaveAs (FileSaveFullPath)
        Call AfterSave
        Application.EnableEvents = True
    End If
    
End Sub

The problem occurs at the point indicated by the asterisks... the file does not save! It only happens when I use the close event (the file should save itself upon closing). Here is my code for the close event:~

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ScreenUpdating = False
    
    If CheckFileIsOpen(strData) = True Then
    
        Set wb = Workbooks(strData)
    
        wb.Windows(1).Visible = True
        With Workbooks(strData)
            .Saved = True
            .Close
        End With 'Workbooks(strDATA)
    
    End If
    
    ThisWorkbook.Sheets("Report").Visible = True
    
    For i = 1 To Sheets.Count
        Select Case Sheets(i).Tab.ColorIndex
            Case 41
                Sheets(i).Visible = True
            Case 1
                Sheets(i).Visible = xlHidden
            Case Else
                Sheets(i).Visible = xlVeryHidden
        End Select
    Next i
    
    If Right(ThisWorkbook.Path, 3) = "zip" Then
        ThisWorkbook.Saved = True
    Else
        ThisWorkbook.Save
    End If

    Application.ScreenUpdating = True

    
End Sub

Any help in explaining this would be gratefully received.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
Re: Why is my file not saving? Still need help.

Bump - sorry, hate to bump my own threads but I really need to understand this - why isn't it working? Pressure at work for a solution is being brought to bear.
 

Airfix9

Well-known Member
Joined
Sep 23, 2005
Messages
886
Re: Why is my file not saving? Still need help.

OK, I have solved it by disabling events on the Close event and handling it in the event itself. Still don't know why my original solution failed. :S
 

Watch MrExcel Video

Forum statistics

Threads
1,132,790
Messages
5,655,318
Members
418,189
Latest member
sam1123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top