PDA

View Full Version : [SOLVED] Prompting to Save



Office User
05-10-2005, 11:15 PM
I'm working with a template file and was able to do some coding to automate the filename used when saving. So far it's working with one exception which comes from the 2nd If.

The goal was if user answers Yes to save file but then doesn't have anything in data5 (which is for Customer Name), it would tell them to update cell. Then they can try again. The code works to the point of displaying my FileSave3 message and I can click OK. However, I still get the "normal" Excel prompt "do you want to save the changes you made to xxxxx". The Application.DisplayAlerts = False worked just fine when user clicks No for first prompt but doesn't seem to want to work for the 2nd prompt.

Here's the code:


Sub Specific_AutoStop()
Const FileSave1 = "Do you want to save this invoice?"
Const FileSave2 = "Your invoice has been saved in c:\Invoices as 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
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
End Sub

Hopefully it's something simple I'm missing. I only know enough VBA to be dangerous : pray2: and am working with this template that someone else originally created.

I have a 2nd question I'll pose as well. Can I add this code somewhere so it's used if someone clicks the normal Save button from toolbar or File menu. Obviously I'd remove the prompt "do you want to save" because that's a given if they're clicking the Save button on their own. That would also mean I would need to change the Close procedure so it checks if the file already has a name so it doesn't save again. What advise would you give on that note?

Thanks for the help,
Marcia

Bob Phillips
05-11-2005, 01:28 AM
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const FileSave2 = "Your invoice has been saved in c:\Invoices as the customer name and date"
Const FileSave3 = "Please enter a Customer Name in order to save the invoice. " _
& vbNewLine & "Click OK then update Customer Name"
Dim Response
Dim sPath As String
sPath = "C:\Invoices\"
Response = MsgBox(FileSave1, vbYesNo + vbQuestion, "Save File?")
If Range("data5").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Customer Name"
Cancel = True
Application.DisplayAlerts = False
Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value & Format(Date, "-mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
ActiveWorkbook.Close
End If
End Sub

This is workbook event code. To input this code, right click on the Excel icon on the worksheet (or next to the File menu if you maximise your workbooks), select View Code from the menu, and paste the code

Steiner
05-11-2005, 02:07 AM
Hi xld,
there seems to be some code missing:
- the text for FileSave1
- the evaluation of the response to the Q whether to save at all
- you're callin Workbook.close inside the before_close event, that causes the event to be called again

So here's my modification:


Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const FileSave1 = "Do you want to save?"
Const FileSave2 = "Your invoice has been saved in c:\Invoices as the customer name and date"
Const FileSave3 = "Please enter a Customer Name in order to save the invoice. " _
& vbNewLine & "Click OK then update Customer Name"
Dim Response
Dim sPath As String
sPath = "e:\test\"
Response = MsgBox(FileSave1, vbYesNo + vbQuestion, "Save File? If no, then it will be closed without saving!")
If Response = vbYes Then
If Range("data5").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Customer Name"
Cancel = True
Application.DisplayAlerts = False
Else
ActiveWorkbook.SaveAs FileName:=sPath & Range("data5").Value & Format(Date, "-mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
End If
Else
'Mark as saved to suppress the prompt to save again
ThisWorkbook.Saved = True
End If
End Sub

Bob Phillips
05-11-2005, 02:25 AM
Hi xld,
there seems to be some code missing:


Obviously I'd remove the prompt "do you want to save" because that's a given if they're clicking the Save button on their own

As such, there should be no promnpt, which I left in, should be


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Const FileSave2 = "Your invoice has been saved in c:\Invoices as the customer name and date"
Const FileSave3 = "Please enter a Customer Name in order to save the invoice." & _
vbNewLine & "Click OK then update Customer Name"
Dim sPath As String
sPath = "C:\Invoices\"
If Range("data5").Value = "" Then
MsgBox FileSave3, vbOKOnly + vbInformation, "Enter Customer Name"
Cancel = True
Application.DisplayAlerts = False
Else
ActiveWorkbook.SaveAs Filename:=sPath & Range("data5").Value & Format(Date, "-mm.dd.yyyy") & ".xls"
MsgBox FileSave2, vbOKOnly + vbInformation, "File Saved"
ActiveWorkbook.Close SaveChanges:=False
End If
End Sub

Steiner
05-11-2005, 04:57 AM
Well, but the event Before_Close is raised when the user selects to close the Workbook, not save. If you put it into Before_Save as you did now, this is of course correct.

Office User
05-11-2005, 09:28 AM
Thanks so much for the input. It appears the Cancel = True is the piece I was missing so if the Customer Name wasn't filled in, it would quit.

I also appreaciate the knowledge of how to use BeforeSave. I had searched for answers and found that Event. However, I didn't think I could use it because it's "before" saving and I actually needed something that would happen "while" saving. I'm learning brain logic isn't the same as computer logic. :bug:

Bob Phillips
05-11-2005, 09:33 AM
I'm learning brain logic isn't the same as computer logic. :bug:

It's exactly the same logic, just in a different order:)

Office User
05-11-2005, 10:13 AM
Guess I spoke too soon. I still get the normal Excel prompt "do you want to save changes to xxxx" after clicking OK for FileSave3 message. I thought the Cancel = True and Application.Display Alerts = False was the answer.

I'm still tackling the Close event. I'll have to try the BeforeSave once I get this
whipped.

Here's the code now. The Sub AutoStop gets called as part of Close which has some other code to close toolbars, check parent/sibling sheet names, etc.


Sub Specific_AutoStop()
Const FileSave1 = "Do you want to save this invoice?"
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"
Cancel = True
Application.DisplayAlerts = 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
End Sub

Zack Barresse
05-11-2005, 10:18 AM
OfficeUser - blah

Office User
05-11-2005, 10:57 AM
Okay I'll do that, but how? When I copy and paste to the forum? Or in my actual code in Visual Basic Editor?

Thanks,
Marcia

Zack Barresse
05-11-2005, 11:08 AM
Copy right from your VBE, then paste as normal text into the reply box. Then just perfom a surround and drown in the VBA Tags. :)

Steiner
05-11-2005, 11:26 PM
Hi Marcia,
here should a part of my 1st post here come in handy:


'Mark as saved to suppress the prompt to save again
ThisWorkbook.Saved = True


This one simply tells Excel that this file has already been saved and so it should not ask again until something else is changed.
So just insert it right before any Workbook.Close where you don't want to be asked.

Daniel

Office User
05-12-2005, 05:36 AM
There must be something in the template's code somewhere else causing the problem because even with ThisWorkbook.Saved = True it comes up with Excel's SaveAs window. Unfortunately, I didn't create the template and was only given it to tweak so the code was written by someone else.

I think I'm just going to remove the BeforeSave code all together and document for the users how and where to save the file.

Thanks for the input. I appreciate the sounding board of this forum.
Marcia