Stop Save As prompt

[Office User]

Trying to customize the saving process when worksheet is closed. If the
customer name cell is empty it should display message and stop process. The
code works to that point but after displaying my FileSave3 message, it still
comes up with Excel's normal prompt "Do you want to save changes made to
xxxx". I thought the Cancel, Alert off and Enable Events would stop it but
isn't.

Here's the code which gets called from Close event. The AutoStop is the
last piece of the Close event.

Sub Specific_AutoStop()

Const FileSave1 = "Do you want to save this invoice? If you click No all
changes will be lost."
Const FileSave2 = "Your invoice has been saved in the folder c:\Invoices.
Please note the file name in the title bar above which includes the customer
name and date"
Const FileSave3 = "Please enter a Customer Name in order to save the
invoice. Click OK then update Customer Name"
Dim Response
Dim sPath As String
sPath = "C:\Invoices\"

Response = MsgBox(FileSave1, vbYesNo + vbQuestion, "Save File?")
If Response = vbNo Then
Application.DisplayAlerts = False
ActiveWorkbook.Close
Else
If Range("data5").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Customer Name"
Application.DisplayAlerts = False
Application.EnableEvents = False
Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value &
Format(Now(), " mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
ActiveWorkbook.Close
End If
End If
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Thanks for the input

 

[Robert Bruce]

Response = MsgBox(FileSave1, vbYesNo + vbQuestion, "Save File?")
If Response = vbNo Then
Application.DisplayAlerts = False
ActiveWorkbook.Close
Else
If Range("data5").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Customer
Name" Application.DisplayAlerts = False
Application.EnableEvents = False
Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value &
Format(Now(), " mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
ActiveWorkbook.Close
End If
End If
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

Your code does not set Application.DisplayAlerts = False before attempting
to save.
--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.

 

[Robert Bruce]

Having reviewed this, it looks like I answered the wrong question.

Your code is called from the close event. Do you mean the BeforeClose event?
Assuming you do, where are you setting the Cancel param to True?


--
Rob

http://www.asta51.dsl.pipex.com/webcam/

This message is copyright Robert Bruce and intended
for distribution only via NNTP.
Dissemination via third party Web forums with the
exception of Google Groups and Microsoft Communities
is strictly prohibited and may result in legal action.