Using Excel 2007 Save As - PDF

[ABabeNChrist]

I have a printing question. I have Office 2007 and I noticed it has an option to Save As PDF.
At the present moment I use this approach to print selected sheets using checkboxes and then set my default printer to PDF Creator.

Code: Select all

Private Sub CommandButton2_Click()

    Dim blnSelected As Boolean
    ActiveWorkbook.Protect Password:="", Structure:=False, Windows:=False

    Application.ScreenUpdating = False

    If CheckBox1.Value And Sheets("Cover Page").Visible = xlSheetVisible Then
        Sheets("Cover Page").Select Replace:=True
        blnSelected = True
    End If
    If CheckBox2.Value And Sheets("Client Information").Visible = xlSheetVisible Then
        Sheets("Client Information").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox3.Value And Sheets("Utilities").Visible = xlSheetVisible Then
        Sheets("Utilities").Select Replace:=False
        blnSelected = True
    End If
    If CheckBox4.Value And Sheets("Grounds").Visible = xlSheetVisible Then
        Sheets("Grounds").Select Replace:=False
        blnSelected = True
    End If

    If blnSelected = True Then

        ActiveWindow.SelectedSheets.PrintOut
        Unload Me
        Unload UserForm4
        Worksheets("Cover Page").Select
        ActiveSheet.Range("D7").Select
        ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                            Password:="", UserInterfaceOnly:=True
        ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

    Else
        MsgBox "No check boxes were selected"

    End If
    Application.ScreenUpdating = True

End Sub

My question is it possible to just use the PDF Save As option that is already available in Office 2007 and if so will I still be able to use a checkbox method to select sheets to print.

[HansV]

There is no built-in method to save selected sheets only to PDF. You could do the following:

Code: Select all

Dim wsh As Worksheet
For Each wsh In ActiveWindow.SelectedSheets
  wsh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=wsh.name & ".pdf"
Next wsh

Note that each sheet will become a separate PDF file.

[ABabeNChrist]

Thank You Hans
I guess that’s where the PDF Creator comes in; it has the ability to combine selected sheets all into one PDF file. I was mostly just curious and was hoping for some possibility, I guess when they designed this feature they didn’t give it much thought of possible usage or maybe it was coffee break time.

[rory]

It was originally a separate add-in and then got shoehorned into SP2. :)