Vba MsgBox - Question for cancelling!

Cuzzaa

Board Regular
Joined
Apr 30, 2019
Messages
86
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!

Code:
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
 
Last edited:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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:

Code:
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
 
Upvote 0
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:

Code:
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!
 
Upvote 0
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...
Code:
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).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top