Update macro button to prompt user to proceed if specific cell is blank

[Cuzzaa]

Hi All

Really hoping you can assist here.

I am using the below vba code for a macro button, essentially once clicked it changes to my 'quotation' worksheet and prints a range of data to PDF.

Sub GenerateQuotate()
'
' GenerateQuotate Macro
'


'
    ActiveWindow.SmallScroll Down:=-3
    Sheets("Quotation").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
    Sheets("Dashboard").Select
    Range("F14").Select
    MsgBox "Your quote has been generated!", vbInformation
End Sub

This is working perfectly, however please could someone assist in what additional code I should use to achieve the following:

When running the macro, I would like it to firstly check if cell O19 is blank, if it is not then resume the macro but if it is blank then prompt the user with a vba msgbox to warn this is blank while providing the user with two options here, either ignore and proceed to run the macro as normal, or cancel the macro and revert back (leaving cell O19 selected) so that the user can then enter data in cell O19.

Many thanks in advance for any help with this.

Cuzzaa

 

[dmt32]

Hi,
try following

Sub GenerateQuotate()
    Dim response As VbMsgBoxResult
' GenerateQuotate Macro
'
    
    With Worksheets("Quotation")
        If Len(.Range("O19").Value) = 0 Then
            response = MsgBox("Range O19 is blank" & Chr(10) & "Do You Want To Continue?", 36, "Cell Blank")
            If response = vbNo Then .Activate: .Range("O19").Select: Exit Sub
      End If
            .PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
            
            MsgBox "Your quote has been generated!", vbInformation
    End With
End Sub

Dave

 

[Cuzzaa]

Hi,
try following

Sub GenerateQuotate()
    Dim response As VbMsgBoxResult
' GenerateQuotate Macro
'
    
    With Worksheets("Quotation")
        If Len(.Range("O19").Value) = 0 Then
            response = MsgBox("Range O19 is blank" & Chr(10) & "Do You Want To Continue?", 36, "Cell Blank")
            If response = vbNo Then .Activate: .Range("O19").Select: Exit Sub
      End If
            .PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
            
            MsgBox "Your quote has been generated!", vbInformation
    End With
End Sub

Dave

Hi Dave

Thanks very much for offering your assistance. I'm afraid I forgot to mention something important; The macro button is on a worksheet called 'Dashboard' though it is printing the data to PDF on a worksheet called 'Quotation'. The cell that I need to check for being left blank is cell O19 on the 'Dashboard' sheet, if this is NOT blank then proceed to run the macro however if cell O19 is left blank then allow the option of cancelling the macro from running via the msgbox.

Based on the above please could you update your code for me? Currently this does not present any msgbox from appearing but I suspect this is because of me failing to tell you the above.

Many thanks.

 

[dmt32]

Hi
No worries always helpful if all relevant information can be provided.

try

Sub GenerateQuotate()
    Dim response As VbMsgBoxResult


' GenerateQuotate Macro
'
    
    With Worksheets("Dashboard")
        If Len(.Range("O19").Value) = 0 Then
            response = MsgBox("Range O19 is blank" & Chr(10) & "Do You Want To Continue?", 36, "Cell Blank")
            If response = vbNo Then .Activate: .Range("O19").Select: Exit Sub
        End If
    End With
    
    Worksheets("Quotation").PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
            
    MsgBox "Your quote has been generated!", vbInformation


End Sub

Dave

 

[Cuzzaa]

Hi
No worries always helpful if all relevant information can be provided.

try

Sub GenerateQuotate()
    Dim response As VbMsgBoxResult


' GenerateQuotate Macro
'
    
    With Worksheets("Dashboard")
        If Len(.Range("O19").Value) = 0 Then
            response = MsgBox("Range O19 is blank" & Chr(10) & "Do You Want To Continue?", 36, "Cell Blank")
            If response = vbNo Then .Activate: .Range("O19").Select: Exit Sub
        End If
    End With
    
    Worksheets("Quotation").PrintOut Copies:=1, Collate:=True, _
            IgnorePrintAreas:=False
            
    MsgBox "Your quote has been generated!", vbInformation


End Sub

Dave

Thanks Dave, that works perfectly now. Very grateful.

Do you know if there is a way that the user is prompted to save the file into a specific folder by default, rather than always opening to the Desktop for e.g?

Thank you

 

[dmt32]

Thanks Dave, that works perfectly now. Very grateful.

Do you know if there is a way that the user is prompted to save the file into a specific folder by default, rather than always opening to the Desktop for e.g?

Thank you

Glad resolved

with regard to other point, there is a way but post as new question - likely to more responses from others here

Dave

 

[Cuzzaa]

Glad resolved

with regard to other point, there is a way but post as new question - likely to more responses from others here

Dave

Understood, thanks Dave.

I have however just realized that using your code you kindly gave me, if cell O19 contains text so that the user is presented with the option to save the file somewhere, if the user clicks cancel then the msgbox is still displayed saying 'Your quote has been successfully generated! - I don't think this used to happen and only popped up when saving the file and clicking Save (I may be wrong). Could this be a result of the code being changed to now include both the Dashboard and Quotation sheets do you know?

Many thanks in advance.

 

[dmt32]

Code just checks for empty cell in O19 & reports msgbox as you requested. If cell contains any Text then your sheet Quotation will printout



Dave

 

[Cuzzaa]

Code just checks for empty cell in O19 & reports msgbox as you requested. If cell contains any Text then your sheet Quotation will printout



Dave

Understood. Thanks again for all your help!