Listbox - Creating PDF

[ashani]

Hi everyone,

I'm using the below coding through listbox to create PDF - it's working fine. However I want sheet ("Home") to be PDF by default at all times regardless of selection in the listbox or not - is it possible ?

Thanks

Sub pdf_sh()
Dim i As Long, c As Long
Dim sheetarray() As String

With ActiveSheet.ListBoxSh
For i = 0 To .ListCount - 1
If .Selected(i) Then
ReDim Preserve sheetarray(c)
sheetarray(c) = .List(i)
c = c + 1
End If

Next i
End With

     pdfName = Sheets("Home").Range("B9").Value & "_" & Sheets("Home").Range("B17")
    ChDir "Test"
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
    fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
        Sheets(sheetarray()).Select
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
            Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    
        Worksheets("Home").Select
        Exit Sub

    End If
  
End Sub

 

[DanteAmor]

Try this:

Sub pdf_sh()
  Dim i As Long, c As Long
  Dim sheetarray() As String
  Dim pdfName As String, fileSaveName As Variant
  
  With ActiveSheet.ListBoxSh
    ReDim Preserve sheetarray(c)
    sheetarray(c) = "Home"
    c = c + 1
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve sheetarray(c)
        sheetarray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
  
  With Sheets("Home")
    pdfName = .Range("B9").Value & "_" & .Range("B17")
    ChDir "Test"
    fileSaveName = Application.GetSaveAsFilename(pdfName, _
      fileFilter:="PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
      Sheets(sheetarray()).Select
      ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fileSaveName, _
          Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
      .Select
    End If
  End With
End Sub

 

[ashani]

@DanteAmor - you're amazing thank you it works perfect.
Sorry last question - is it possible to have the same for another 2 worksheets (Sheet1 & Sheet2) export as PDF by default at all times. So in total 3 sheets by defaults - Home, Sheet1 & Sheet2.

Thank you once again.

 

[DanteAmor]

Try this:

Sub pdf_sh()
  Dim i As Long, c As Long
  Dim sheetarray() As String
  Dim pdfName As String, fileSaveName As Variant
  Dim default As Variant
  
  default = Array("Home", "Sheet1", "Sheet2")
  
  With ActiveSheet.ListBoxSh
    For i = 0 To UBound(default)
      ReDim Preserve sheetarray(c)
      sheetarray(c) = default(i)
      c = c + 1
    Next
    
    For i = 0 To .ListCount - 1
      If .Selected(i) Then
        ReDim Preserve sheetarray(c)
        sheetarray(c) = .List(i)
        c = c + 1
      End If
    Next i
  End With
  
  With Sheets("Home")
    pdfName = .Range("B9").Value & "_" & .Range("B17")
    ChDir "Test"
    fileSaveName = Application.GetSaveAsFilename(pdfName, "PDF Files (*.pdf), *.pdf")
    If fileSaveName <> False Then
      Sheets(sheetarray()).Select
      ActiveSheet.ExportAsFixedFormat xlTypePDF, fileSaveName, xlQualityStandard, True, False, , , True
      .Select
    End If
  End With
End Sub