Save Fail on Excel Close

[Kardan]

I have a workbook that requires macros enabled to use properly.

I have created a worksheet with a warning message that will only be hidden by
a startup macro and to ensure this displays, I have the following code for
the BeforeSave event that shows the sheet, hides all others and then reverses
this after the save.

This works great when the user chooses Save or SaveAs, however when the user
closes the workbook and chooses to save, the save does not happen. The save
name for a new workbook does not get passed and SaveUI has a value of False.

Does anyone have any ideas why this should happen and what I can do to solve
this issue?



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

Application.EnableEvents = False

'<Optional - this would be before save code>
If SaveAsUI Then
sFile = Application.GetSaveAsFilename(, "Excel Files (*.xls), *.xls")
End If

SetMacroWarn

If SaveAsUI Then
If sFile <> False Then
ThisWorkbook.SaveAs sFile
ThisWorkbook.Saved = True
'<Optional - this would be after save code>
RemoveMacroWarn

End If
Else
ThisWorkbook.Save
ThisWorkbook.Saved = True
'<Optional - this would be after save code>
RemoveMacroWarn
End If
Application.EnableEvents = True

Cancel = True

End Sub

 

[Barb Reinhardt]

Try this (I commented out a couple of lines of code the procedures were not
available to test)

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

Application.EnableEvents = False

'<Optional - this would be before save code>
If SaveAsUI Then
sFile = Application.GetSaveAsFilename(, "Excel Files (*.xls), *.xls")
End If


'SetMacroWarn

If SaveAsUI Then
If sFile <> False Then
Application.displayalerts = false
ThisWorkbook.SaveAs sFile
Application.displayalerts = True
ThisWorkbook.Saved = True
'<Optional - this would be after save code>
'RemoveMacroWarn

End If
End If
If Cancel = True Or SaveAsUI = False Then
ThisWorkbook.Save
ThisWorkbook.Saved = True
'<Optional - this would be after save code>
'RemoveMacroWarn
Cancel = False
End If
Application.EnableEvents = True

End Sub