vb Cancel change

[andrewb90]

I have a macro with a vbYesNo in it currently, I tried adding cancel as an option (vbYesNoCancel) but ran into some issues.
Can anybody assist me in making my code run with the Cancel option?
All I need is to have the cancel option there and have the sub end if cancel is selected.

Sub Sendit()
Dim lr As Long
' Conformation Box for Daily Sales Report Date
strPrompt = "The date for the Daily Sales Report is " & Range("B9") & " " & Range("C9") & vbNewLine & "Do you want to save with this date?"
strTitle = "Date Confirmation"
iRet = MsgBox(strPrompt, vbYesNo, strTitle)
If iRet = vbNo Then 'Date is incorrect
    MsgBox ("Please type the correct date that you want to use then click on the oversized clock.")
    Rows("7:85").Hidden = True
    Columns("F").Hidden = True
    Rows("90:91").Hidden = False
    ActiveSheet.Shapes("Jumbo Clock").Visible = True
    Range("A1").Select

'vbCancel????

Else 'Date is correct'
    Rows("7:60").Hidden = False
    Columns("F").Hidden = False
    Rows("90:91").Hidden = True
    ActiveSheet.Shapes("Jumbo Clock").Visible = False
    MsgBox ("Saving...Please Wait...")
    'This sends the info from the Midway sheet(The current numbers that are on the Daily sheet)
    ' to the Accounting Summary sheet. ***The Midway sheet reorganizes the info for record keeping***
    lr = Sheets("Midway").Cells(Rows.Count, 7).End(xlUp).Row
    Sheets("Summary").Range("c2").EntireColumn.Insert
    Sheets("Midway").Range("C9:C28").Copy
    Sheets("Summary").Range("C6").PasteSpecial Paste:=xlPasteValues 'The row that it starts to paste
    With Sheets("Summary")
    .Range("D1").EntireColumn.Copy
    .Range("C1").PasteSpecial Paste:=xlPasteFormats
    End With
    'This clears out the Daily Sheet
    Sheets("Daily").Range("D11:F40").ClearContents
    Sheets("Daily").Range("F48").Select
    ActiveCell.FormulaR1C1 = ""
    Sheets("Daily").Range("F50:G52").ClearContents
    Sheets("Daily").Range("J11:L42").ClearContents
    Sheets("Daily").Range("J44:L55").ClearContents
    'Sheets("Daily").Range("B49").ClearContents
    Application.CutCopyMode = False
    'This sets the starting amount for the deposit at $125
    Range("D44").Select
    ActiveCell.FormulaR1C1 = "125"
    Range("E44").Select
    ActiveCell.FormulaR1C1 = "125"
    Range("F44").Select
    ActiveCell.FormulaR1C1 = "125"
    'Assigning the new Date
    With Range("G90")
      .Value = Date - 1
      .NumberFormat = "mm/dd/yyyy"
      .Offset(, -1).FormulaR1C1 = "=TEXT(RC[1],""dddd"")"
    Range("B9:C9").Select
    End With

    'Sort sales data on the Accounting Summary sheet
    Sheets("Summary").Select
    Range("C7", Cells(7, Columns.Count).End(xlToLeft)).Sort _
            Cells(7, 3), xlDescending, Orientation:=xlSortRows

    Sheets("Daily").Select
    Range("G9").Select
    MsgBox ("The Daily Sales report has been archived and is now ready for use.")
End If

End Sub

 

[jproffer]

I'm not going to repost all of that, but it would be something like:

Select Case iRet
    Case vbYes
        'do something for yes
    Case vbNo
        'do something for no
    Case vbCancel
        Exit Sub
End Select

 

[andrewb90]

Do my strPrompt and strTitle stay in?

I'm just not entirely sure where I would put this in.

I took out all the extra code that I don't need:

Sub Sending()
Dim lr As Long
' Conformation Box for Daily Sales Report Date
strPrompt = "The date for the Daily Sales Report is:"
strTitle = "Date Confirmation"
iRet = MsgBox(strPrompt, vbYesNo, strTitle)

If iRet = vbNo Then 'Date is incorrect
    MsgBox ("Please type the correct date.")

Else 'Date is correct'
    Rows("7:60").Hidden = False

End If

End Sub

 

[RoryA]

You need to alter the msgbox part to use vbyesnocancel and then have a line like:

If iret = vbcancel then exit sub

 

[andrewb90]

Using this code: I am running into an issue with getting my message boxes to appear.

Sub Sending()
Dim lr As Long
' Conformation Box for Daily Sales Report Date
strPrompt = "Testing:"
strTitle = "Date Confirmation"
iRet = MsgBox(strPrompt, vbYesNoCancel, strTitle)



If iRet = vbNo Then 'Date is incorrect
    MsgBox ("No!!!")
    


If iRet = vbYes Then 'Date is correct'
    MsgBox ("Yes!!!")
    
If vbCancel Then Exit Sub

End If
End If

End Sub

 

[GTO]

Try:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Sending()<br><SPAN style="color:#00007F">Dim</SPAN> lr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, iRet <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> strPrompt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strTitle <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#007F00">' Conformation Box for Daily Sales Report Date</SPAN><br>strPrompt = "Testing:"<br>strTitle = "Date Confirmation"<br>    <br>    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> MsgBox(strPrompt, vbYesNoCancel, strTitle)<br>    <SPAN style="color:#00007F">Case</SPAN> vbNo<br>        MsgBox "No!!!"<br>    <SPAN style="color:#00007F">Case</SPAN> vbYes<br>        MsgBox "Yes!!!"<br>    <SPAN style="color:#00007F">Case</SPAN> vbCancel<br>        MsgBox "I exited if I chose cancel"<br>        <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[andrewb90]

Awesome! Thank you!

 

[GTO]

Happy to help and thank you for the confirmation

Mark