mardi 29 janvier 2019

VBA - Check if range is filled, then generate PDF

I would like to ask you for a help. I have code that generates PDF and that works fine, but I would like to add condition. User should fill cells D15, D17, D19 and D21, if they are blank, then it should notify the user by MsgBox. If they are filled then it should continue to generate the PDF file.

I've tried to put the condition, but it gives me an error msg Wrong number of arguments or invalid property assignment on line Set rng = .Range("D15", "D17", "D19", "D21")

The full code is:

Private Sub CBSaveasPDF_Click()
    Dim sPath As String
    Dim sFile As Variant
    Dim ws As Worksheet
    Dim rng As Range

    With Worksheets("Dashboard")
    Set rng = .Range("D15", "D17", "D19", "D21")
    End With

    On Error GoTo ErrHandle

    If IsEmpty(rng) Then
                        MsgBox ("Please fill the yellow cells")
                    Exit Sub
    Else

    sPath = ThisWorkbook.Path & "\" & Me.Range("D9") & " -" & Me.Range("D8") & " -" & Me.Range("J8") & " " & Me.Range("B4")

    sFile = Application.GetSaveAsFilename _
                (InitialFileName:=sPath, _
            FileFilter:="PDF Files (*.pdf), *.pdf", _
            Title:="Select Folder and FileName to save")
        If sFile = "False" Then
            MsgBox ("Document not saved")
            Exit Sub
        End If

        Me.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=sFile, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True
    End If

        Exit Sub
ErrHandle:
    MsgBox ("Document Not Saved")
End Sub

Could you advise me, how should I define the range better, please?

Many thanks!

Aucun commentaire:

Enregistrer un commentaire