I am using the vbYesNoCancel msgbox, and I'm curious if there is a wa
to make it so that I have more options...

I want to have it so that if you press Yes it does all of th
following: (Saves workbook, sends sheet to outlook for email, send
values to new workbook), if you answer no, it does the first two, i
you click cancel it just does the first, and then I want to be able t
just close it altogether without it doing anything. This worked fin
when I just had three options, basically saves workbook and sends shee
to oulook were one and the same, but I'd like to be able to isolat
those two ideas. Here is what I have right now...
Code
-------------------
Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim ans
ans = MsgBox("Do you want to save the current Quote and convert to an Invoice? Answer No to Save the Quote and Send to Outlook for a Customer. Cancel to go back", vbYesNoCancel, "Save Quote/Convert to an Invoice")
If ans = vbNo Then
Dim MyName3
MyName3 = "Quote " + Sheets("Quote").Range("D13").Text + " " + Sheets("Quote").Range("O4").Text
ActiveWorkbook.SaveAs _
Filename:="C:\SyntheticShield\Quotes\" & MyName3, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="Amsoil", _
CreateBackup:=False
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy ")
Application.ScreenUpdating = False
Sheets("Quote").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Customer " & ThisWorkbook.Name
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Your Synthetic Shield Quote for your " + Sheets("Quote").Range("E47").Text + " " + Sheets("Quote").Range("E49").Text + " " + Sheets("Quote").Range("E51").Text
.Body = "Here is your Synthetic Shield Quote as you requested. Thanks Again for your business."
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
Dim ans5
ans5 = MsgBox("Do you want to create another Quote?", vbYesNo, "New Quote")
If ans5 = vbNo Then
ActiveWorkbook.Close False
End If
End If
If ans = vbYes Then
Workbooks.Open Filename:="C:\SyntheticShield\SyntheticShieldInvoi ce.XLT"
Set wbk = ActiveWorkbook
With wbk
Sheets("Sales Invoice").Range("D13:H13").Value = ThisWorkbook.Sheets("Quote"). _
Range("D13:H13").Value
ActiveWindow.ActivatePrevious
Dim MyName2
MyName2 = "Quote" + Sheets("Quote").Range("D13").Text + Sheets("Quote").Range("O4").Text
'(This value will be the file’s name.)
ActiveWorkbook.SaveAs _
Filename:="C:\SyntheticShield\Quotes\" & MyName2, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="Amsoil", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Dim ans4
ans4 = MsgBox("Do you want to close the QuoteMaker?", vbYesNo, "Close SyntheticShield QuoteMaker?")
If ans4 = vbYes Then
ActiveWorkbook.Close False
End If
End With
End If
End Su
-------------------


--
Message posted from http://www.ExcelForum.com

boxAns = MsgBox (...)
If boxAns = vbYes Then
'saves workbook
'sends mail
'values into new workboook
Elseif boxAns = vbNo
'saves workbook
'sends mail
Elseif boxAns = vbCancel Then
'saves workbook
'sends mail
End If

or as all save the workbook then

boxAns = MsgBox (...)
'saves workbook
If boxAns = vbYes Then
'sends mail
'values into new workboook
Elseif boxAns = vbNo
'sends mail
Elseif boxAns = vbCancel Then
'sends mail
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"RPIJG " wrote in message
...
I am using the vbYesNoCancel msgbox, and I'm curious if there is a way
to make it so that I have more options...

I want to have it so that if you press Yes it does all of the
following: (Saves workbook, sends sheet to outlook for email, sends
values to new workbook), if you answer no, it does the first two, if
you click cancel it just does the first, and then I want to be able to
just close it altogether without it doing anything. This worked fine
when I just had three options, basically saves workbook and sends sheet
to oulook were one and the same, but I'd like to be able to isolate
those two ideas. Here is what I have right now...
Code:
--------------------
Private Sub CommandButton1_Click()
Dim wbk As Workbook
Dim ans
ans = MsgBox("Do you want to save the current Quote and convert to an
Invoice? Answer No to Save the Quote and Send to Outlook for a Customer.
Cancel to go back", vbYesNoCancel, "Save Quote/Convert to an Invoice")
If ans = vbNo Then
Dim MyName3
MyName3 = "Quote " + Sheets("Quote").Range("D13").Text + " " +
Sheets("Quote").Range("O4").Text
ActiveWorkbook.SaveAs _
Filename:="C:\SyntheticShield\Quotes\" & MyName3, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="Amsoil", _
CreateBackup:=False
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim wb As Workbook
Dim strdate As String
strdate = Format(Now, "dd-mm-yy ")
Application.ScreenUpdating = False
Sheets("Quote").Copy
Set wb = ActiveWorkbook
With wb
.SaveAs "Customer " & ThisWorkbook.Name
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Your Synthetic Shield Quote for your " +
Sheets("Quote").Range("E47").Text + " " + Sheets("Quote").Range("E49").Text
+ " " + Sheets("Quote").Range("E51").Text
.Body = "Here is your Synthetic Shield Quote as you requested. Thanks
Again for your business."
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
.ChangeFileAccess xlReadOnly
Kill .FullName
.Close False
End With
Application.ScreenUpdating = True
Set OutMail = Nothing
Set OutApp = Nothing
Dim ans5
ans5 = MsgBox("Do you want to create another Quote?", vbYesNo, "New
Quote")
If ans5 = vbNo Then
ActiveWorkbook.Close False
End If
End If
If ans = vbYes Then
Workbooks.Open Filename:="C:\SyntheticShield\SyntheticShieldInvoi ce.XLT"
Set wbk = ActiveWorkbook
With wbk
Sheets("Sales Invoice").Range("D13:H13").Value =
ThisWorkbook.Sheets("Quote"). _
Range("D13:H13").Value
ActiveWindow.ActivatePrevious
Dim MyName2
MyName2 = "Quote" + Sheets("Quote").Range("D13").Text +
Sheets("Quote").Range("O4").Text
'(This value will be the file's name.)
ActiveWorkbook.SaveAs _
Filename:="C:\SyntheticShield\Quotes\" & MyName2, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="Amsoil", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
Dim ans4
ans4 = MsgBox("Do you want to close the QuoteMaker?", vbYesNo, "Close
SyntheticShield QuoteMaker?")
If ans4 = vbYes Then
ActiveWorkbook.Close False
End If
End With
End If
End Sub
--------------------



---
Message posted from http://www.ExcelForum.com/