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