Vba MsgBox - Question for cancelling!

[Cuzzaa]

Hi everyone

Hope you can help.

I am using a button to run the below code which prints specific data to a PDF, though first it checks to see if cell M21 is blank. If it is then it prompts the user to warn them if they want to proceed or cancel. If they proceed or if cell M21 is NOT blank then the user is greeted by a Msg box saying 'Your Quote Has Been Generated!'

The problem is, if you click cancel at any point or do not print to PDF then it still says 'Your Quote Has Been Generated!'.

Please can someone help amend my code so that it says 'Your Quote Has Been Generated!' ONLY if you save the PDF? Otherwise present the user with 'Quote Cancelled!' if you click on cancel OR click cancel while being promoted to save the PDF? I can't seem to get the code right

Thank you so much!

Sub NewQuoteGenerate()

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet


    Application.ScreenUpdating = False
    Dim response As VbMsgBoxResult
    With Worksheets("Dashboard")
        If Len(.Range("M21").Value) = 0 Then
            response = MsgBox("You have not added any caveats or assumptions!" & Chr(10) & Chr(10) & "Are you sure you want to continue?", 36, "Caveats & Assumptions")
            If response = vbNo Then .Activate: .Range("M21").Select: Exit Sub
        End If
    End With
    Sheets("Quotation").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ChDir "Z:\General\Folderlocation\"
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True
    
    Call sourceSheet.Activate
    
End Sub

 

[sykes]

The code doesn't seem to allow for instructions on what to do in the event that the user cancels - only if they press the "No" button.
At the moment, if the user presses "No" then the code quits, but if they press "Cancel" it continues to run.
Also, when I ran your code, it only bought up yes/no buttons - as per the arguments which sum to your "36."

Suggest that you use "35" instead (yes/no/cancel), and add the possibility of a "Cancel" response - using "OR" thus:

Sub NewQuoteGenerate()

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet


    Application.ScreenUpdating = False
    Dim response As VbMsgBoxResult
    With Worksheets("Dashboard")
        If Len(.Range("M21").Value) = 0 Then
            response = MsgBox("You have not added any caveats or assumptions!" & Chr(10) & Chr(10) & "Are you sure you want to continue?", 35, "Caveats & Assumptions")
            If response = vbNo Or vbCancel Then .Activate: .Range("M21").Select: Exit Sub
        End If
    End With
    Sheets("Quotation").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ChDir "Z:\General\Folderlocation\"
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True
    
    Call sourceSheet.Activate
    
End Sub

 

[Cuzzaa]

The code doesn't seem to allow for instructions on what to do in the event that the user cancels - only if they press the "No" button.
At the moment, if the user presses "No" then the code quits, but if they press "Cancel" it continues to run.
Also, when I ran your code, it only bought up yes/no buttons - as per the arguments which sum to your "36."

Suggest that you use "35" instead (yes/no/cancel), and add the possibility of a "Cancel" response - using "OR" thus:

Sub NewQuoteGenerate()

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet


    Application.ScreenUpdating = False
    Dim response As VbMsgBoxResult
    With Worksheets("Dashboard")
        If Len(.Range("M21").Value) = 0 Then
            response = MsgBox("You have not added any caveats or assumptions!" & Chr(10) & Chr(10) & "Are you sure you want to continue?", 35, "Caveats & Assumptions")
            If response = vbNo Or vbCancel Then .Activate: .Range("M21").Select: Exit Sub
        End If
    End With
    Sheets("Quotation").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ChDir "Z:\General\Folderlocation\"
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True
    
    Call sourceSheet.Activate
    
End Sub

Hi Sykes!

Thank you for offering your assistance here. I have tried using your code, however on Msgbox pop up if I click Yes to continue (if M21 is blank) then it doesn't seem to do anything. Also if M21 is NOT blank then once the user is prompted with the location to save and you click cancel (instead of save) then it says as a PDF regardless when it should cancel the action instead.

Please can you help me try and fix the above? Also, rather than having Yes, No & Cancel is there a way to only have Yes and Cancel (as No and Cancel are doing the same thing in a way)? And also I'd like it to cancel out of the macro without any errors if on the popup asking where to save the PDF the user clicks Cancel instead of Save. Can this be done?

Thanks again for all your help I'm truly grateful!

 

[Rick Rothstein]

Hi Sykes!

Thank you for offering your assistance here. I have tried using your code, however on Msgbox pop up if I click Yes to continue (if M21 is blank) then it doesn't seem to do anything. Also if M21 is NOT blank then once the user is prompted with the location to save and you click cancel (instead of save) then it says as a PDF regardless when it should cancel the action instead.

I think Sykes accidentally omitted what I show in red below...

Sub NewQuoteGenerate()

Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet


    Application.ScreenUpdating = False
    Dim response As VbMsgBoxResult
    With Worksheets("Dashboard")
        If Len(.Range("M21").Value) = 0 Then
            response = MsgBox("You have not added any caveats or assumptions!" & Chr(10) & Chr(10) & "Are you sure you want to continue?", 35, "Caveats & Assumptions")
            If response = vbNo Or [B][COLOR="#FF0000"]response =[/COLOR][/B] vbCancel Then .Activate: .Range("M21").Select: Exit Sub
        End If
    End With
    Sheets("Quotation").PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
    ChDir "Z:\General\Folderlocation\"
    Sheets("Quotation").ExportAsFixedFormat Type:=xlTypePDF, Filename:=ActiveSheet.Name _
        , Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=True
    MsgBox "Your quote has been generated!", vbInformation
    Application.ScreenUpdating = True
    
    Call sourceSheet.Activate
    
End Sub

NOTE: I do not know about all those commands linked with colons following the "Then" keyword as I do not know multiple commands lumped into one line like that (too easy to miss something when reviewing code months or years later).