GetSaveAsFile

[Daviv]

I am trying to do the following:

Ask the user when the user is closin the file if the save had been saved.

If yes, close the file and quit application.
If no, then have the SaveAs dialog box appear with a preset

filename and file directory. If the user is OK with filename and directory,
the user saves file; application quit.

If cancel, exit sub.

This is what I have so far

If MsgBox("Have you save this file?", vbYesNo) = vbNo Then
sName = Range("File").Value & Range("Project").Value & "
" & "Insp. Report," & " " & Format(Report(0, 1), "d-mmm-yy")
res =
Application.GetSaveAsFilename(InitialFileName:=sName)
ActiveWorkbook.SaveAs Filename:=res
Application.Quit
Cancel = True
Exit Sub
Else
ThisWorkbook.Saved = True
Application.Quit
End If

 

[Chip Pearson]

Try something like the following:


Sub AAA()

Dim FName As Variant
Dim MsgResult As VbMsgBoxResult
Dim InitFileName As String

InitFileName = "C:\Test.xls" '<<<< CHANGE THIS
MsgResult = MsgBox("Have you saved the file?", vbYesNoCancel)
Select Case MsgResult
Case vbNo
FName = Application.GetSaveAsFilename( _
InitFileName, "Excel File (*.xls),*.xls")
If FName = False Then
' user cancelled from SaveAs dialog.
Exit Sub
Else
With Application
.DisplayAlerts = False
.EnableEvents = False
ActiveWorkbook.SaveAs Filename:=FName
.EnableEvents = True
.DisplayAlerts = True
' .Quit
End With
End If

Case vbYes
ThisWorkbook.Saved = True
'Application.Quit
Case vbCancel
Exit Sub
End Select

End Sub

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


(e-mail address removed)...

 

[Daviv]

Thanks for the response. I have another question. When I hit the cancel
button for both 1) vbyesnocancel and 2) saveas dialog, I get this message,
"Do you want to save the changes you made to "this file". What I want to
occur when I hit the cancel button is to exit the sub only. It is cumbersome
to hit the "cancel' button again.