Save selected sheets from a listbox to pdf

[ob1tech77]

Hello everyone! I have a listbox that I use to preview and print selected sheets. I'm trying to use the same listbox and adding the ability to save selected sheets to pdf as well (user input location to save file ). I'm attaching pictures of my current setup. Is this possible? Your help would be greatly appreciated.

 

[Micron]

I would say very possible. However, you should help others to help you by posting code that anyone might work with, not pictures of code and have to type it all out when you already have done that. Posted code needs to be within code tags (vba button on posting toolbar) otherwise the forum will remove all indentation.

Or you can review Worksheet.ExportAsFixedFormat method (Excel) and give it a go.

 

[ob1tech77]

I would say very possible. However, you should help others to help you by posting code that anyone might work with, not pictures of code and have to type it all out when you already have done that. Posted code needs to be within code tags (vba button on posting toolbar) otherwise the forum will remove all indentation.

Or you can review Worksheet.ExportAsFixedFormat method (Excel) and give it a go.

Sorry I'm new to posting here. Here is the code I'm using to print the listbox. Let me know if this is the format I'm supposed to use. Thanks for clarifying.

Sub Print_Sheet2s()
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
        Sheets(SheetArray()).PrintOut
        
        'If you'd like to print out
        'Sheets(SheetArray()).PrintOut
    End With
End Sub

 

[Micron]

I guess you got that code from somewhere? I'm not as proficient with Excel vba compared to Access vba, so I'm not seeing the need for an array.
This will loop over Listbox1 in Userform4 and if the listbox allows multi select, it should create or overwrite a pdf without prompting.
Note, I don't usually give my test objects meaningful names because it's likely I'll never use them again, but maybe I should make the effort.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim ws As Worksheet

With Me.ListBox1
    For i = 0 To Me.ListBox1.ListCount - 1
        If .Selected(i) Then
            Set ws = Worksheets(.List(i))
            ws.ExportAsFixedFormat xlTypePDF, .List(i)
        End If
    Next
End With

This is how I get the sheet names into the control:

Private Sub UserForm_Initialize()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    Me.ListBox1.AddItem ws.Name
Next

End Sub

I find it odd that the list does not replicate itself and I continue to wonder why Excel doesn't have Access' ItemsSelected property. Then you wouldn't have to loop over a listbox and check if something is selected.

You could test that on a wb copy and see if it's a good start. As for the user input for directory location, consider using msoFileDialogFolderPicker (google it)