Export contents of listbox to pdf

Question

I have a userform that contains a listbox. My workbook has 2 worksheets and my listbox grabs data from both sheets depending what I am searching for.

How do I export the contents of my listbox to pdf regardless of worksheet?

I found this generic macro that can be assigned to a command button in a userform but it will only export contents from active sheet and not content in the listbox.

Sub PDFActiveSheet()
Dim ws As Worksheet
Dim strPath As String
Dim myFile As Variant
Dim strFile As String
On Error GoTo errHandler

Set ws = ActiveSheet

'enter name and select folder for file
' start in current workbook folder
strFile = Format(Now(), "yyyymmdd\_hhmm") _
            & ".pdf"
strFile = ThisWorkbook.Path & "\" & strFile

myFile = Application.GetSaveAsFilename _
    (InitialFileName:=strFile, _
        FileFilter:="PDF Files (*.pdf), *.pdf", _
        Title:="Select Folder and FileName to save")

If myFile <> "False" Then

    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=myFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False

    With ws.PageSetup
        .CenterHeader = "Report"
        .Orientation = xlLandscape
        .Zoom = True
        .FitToPagesTail = False
        .FitToPagesWide = 1
    End With

    MsgBox "PDF file has been created."
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler
End Sub

What are you expecting your end result to look like? Just a couple words on a page? Why do you need to export a listbox's content? Would you be okay with just writing those contents to a new worksheet then converting that? — dwirony
@dwirony my end result would be a report taken from the listbox content. Would not want to insert another sheet into the workbook as the workbook will not be available to users and don't really want to keep adding worksheets every time a user wants to generate a report — LinuxPingu

LinuxPingu LinuxPingu · Accepted Answer · 2018-12-28T11:26:27

Thank you I have managed to come up with another easier solution. Just exporting the listbox contents to excel

Private Sub SaveListBoxContent_Click()

    Dim i As Integer
    Dim xlApp As Excel.Application
    Dim xlSh As Excel.Worksheet

    Set xlApp = New Excel.Application
    xlApp.Visible = True
    xlApp.Workbooks.Add

    Set xlSh = xlApp.Workbooks(1).Worksheets(1)
     For i = 1 To Me.ListBox1.ListCount
        xlSh.Cells(i, 1).Value = Me.ListBox1.List(i - 1, 0)
        xlSh.Cells(i, 2).Value = Me.ListBox1.List(i - 1, 1)
        xlSh.Cells(i, 3).Value = Me.ListBox1.List(i - 1, 2)
        xlSh.Cells(i, 4).Value = Me.ListBox1.List(i - 1, 3)
        xlSh.Cells(i, 5).Value = Me.ListBox1.List(i - 1, 4)
        xlSh.Cells(i, 6).Value = Me.ListBox1.List(i - 1, 5)

    Next

End Sub

Export contents of listbox to pdf

1 Answers