Save Event Handler

[Spencer]

I am trying to "force" my user to use the "save" subroutine i have written
which utilizes the GetSaveAsFilename method with a customized dialog and a
default filename. is there a way to bypass excel asking the user if they
want to save if they either try to close the workbook or click the save
button.
i attempted this already using the workbook_beforeclose event handler and in
that subroutine, put my getsaveasfilename method and it prompeted the user to
save again and when it went to close the workbook finally, ran through the
beforeclose event handler again. any help is appreciated, TIA

 

[Chip Pearson]

Try code like the following in the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim FName As Variant
On Error GoTo ExitSub
If SaveAsUI = True Then
Cancel = True
FName = Application.GetSaveAsFilename(FileFilter:="Excel Files
(*.xls),*.xls")
If FName = False Then
' user cancelled
Else
Application.DisplayAlerts = False
Application.EnableEvents = False
ThisWorkbook.SaveAs Filename:=FName
End If
End If

ExitSub:

Application.EnableEvents = True
Application.DisplayAlerts = True

End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)