mercredi 25 novembre 2015

Check boxes in user form to select sheets to print

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




Aucun commentaire:

Enregistrer un commentaire