Check boxes in user form to select sheets to print

Question

I have code that selects an array of sheets to be printed to a pdf document, however I am trying to implement a user form with a series of check boxes that correspond to a specific sheets.

The following code selects a predetermined array of sheets and prints them as a pdf

Sub PDFAllSheets_Click()

Dim ws As Worksheet
Dim strPath As String
Dim myfile As Variant
Dim strFile As String
Dim sheetstoprint As String

On Error GoTo errHandler

Set ws = ActiveSheet

strFile = "E_CALC_" & Worksheets("Contents").Range("H7").Text & ".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

    ThisWorkbook.Sheets(Array("Engine", "CHP Layout", "Ventilation", "Exhaust", "Gas", "Hazardous Zoning", "Gas Ramp up", "Steam Boilers", _
                        "JW PU", "AC PU", "Combustion", "BREEAM NOx", "Pump P1", "Pump P2", "Pump P3", "Pump P4", "Pump P5")).Select

    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    FileName:=myfile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

    MsgBox "PDF file has been created."

End If

exitHandler:
Exit Sub

errHandler:
MsgBox "Could not create PDF file", vbRetryCancel, "Oops!"

Resume exitHandler

End Sub

I need the following UserForm's check boxes to define the sheets to include in the array.

UserForm1

Answer 1

If you have a UserForm with a ListBox and a CommandButton, this should work, assuming you have sheets named as you specified.

This code should of course be added to the UserForm code module.

Private Sub CommandButton1_Click()

Dim SheetArray() As Variant
Dim indx As Integer

Dim ws As Worksheet
Dim strPath As String
Dim myfile As Variant
Dim strFile As String
Dim sheetstoprint As String

On Error GoTo errHandler

Set ws = ActiveSheet

strFile = "E_CALC_" & Worksheets("Contents").Range("H7").Text & ".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

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    indx = 0
    For i = 0 To ListBox1.ListCount - 1
            If ListBox1.Selected(i) = True Then
                ReDim Preserve SheetArray(indx)
                SheetArray(indx) = Sheets(ListBox1.List(i, 1)).Index
                indx = indx + 1
            End If
    Next i

    If indx > 0 Then

            Sheets(SheetArray()).Select
                ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=myfile, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=True


                '.ExportAsFixedFormat Type:=xlTypePDF, Filename:=myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True


    End If
End If
exitHandler:
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Exit Sub

errHandler:
MsgBox "Could not create PDF file", vbRetryCancel, "Oops!"

Resume exitHandler
End Sub

Private Sub UserForm_Initialize()

Dim wks() As Variant


wks = Array("Engine", "CHP Layout", "Ventilation", "Exhaust", "Gas", "Hazardous Zoning", "Gas Ramp up", "Steam Boilers", _
                        "JW PU", "AC PU", "Combustion", "BREEAM NOx", "Pump P1", "Pump P2", "Pump P3", "Pump P4", "Pump P5")

'Debug.Print wks(16)
For i = 0 To UBound(wks)

        ListBox1.AddItem wks(i)
        ListBox1.List(ListBox1.ListCount - 1, 1) = wks(i)

Next i


End Sub

Remember to allow listbox multiselect in the listbox properties window.

Edit: During my testing, it seems that the Excel application freezes after exporting the PDF. I don't know if it has anything to do with the OpenAfterPublish property being set to True as I've always set it to False.

Edit2:

My mistake, It's simply because the UserForm is still open ...