Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Save As prompt
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Save As prompt
Roedd <<Office User wedi ysgrifennu:
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop Save As prompt
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't stop the Update Links? startup prompt | Excel Discussion (Misc queries) | |||
stop prompt to save on no changes made | Excel Discussion (Misc queries) | |||
save prompt for user exit, but no save prompt for batch import? | Excel Discussion (Misc queries) | |||
Stop the prompt to save when closing workbooks | Excel Discussion (Misc queries) | |||
Stop prompt for password when Excel closes | Excel Programming |