Solved: Save Worksheet selection to PDF

[Hotnumbers]

I have a user form with 12 check boxes. the 12 selections are the months of the year which correspond to worksheets. I need to be able to save the worksheet as a PDF with. Can someone please point me to the right direction or if you have code that does this process can you please share... I have done research on the net with no luck.

[Bob Phillips]

See if there is anything on Ken's site http://www.excelguru.ca/list.php?category/75-PDF-Creator-VBA-Programming

[Kenneth Hobs]

Depends on your Excel version. For 2007+ replace the Value2 part and the activeworksheet with Worksheets("yourcontrolvaluewiththesheetnamehere").

Sub Test_PublishToPDF()
Dim sDirectoryLocation As String, sName As String

sDirectoryLocation = ThisWorkbook.Path
sName = sDirectoryLocation & "\" & Range("E4").Value2 & ".pdf"
PublishToPDF sName, activeworksheet
End Sub


Sub PublishToPDF(fName As String, ws As Worksheet)
Dim rc As Variant

'ChDrive "c:"
'ChDir GetFolderName(fName)
rc = Application.GetSaveAsFilename(fName, "PDF (*.pdf), *.pdf", 1, "Publish to PDF")
If Not rc Then Exit Sub

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

[Hotnumbers]

Kenneth....

I am running 2007. I am not sure what you mean.


For example

I have a user forms. for CheckBox 1 Below is the code... I would i intergrade your code into this specific CheckBox so it saves as PDF?

If CheckBox1.Value = True Then
Sheets("October").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If

[Kenneth Hobs]

Did you not want to save the worksheet as a pdf? If so, you need to specify what the PDF filename is.

In this example, I used the Tag property of the checkbox control to hold the sheet name and the base name for the PDF file. You can also use the Caption property of the checkbox control if you like. Obviously, you will need to change the drive and path name for fName.

Private Sub CommandButton1_Click()
Dim i As Integer, c As Control, fName As String
For i = 1 To 12
Set c = Controls("CheckBox" & i)
If c.Value = True Then
fName = "x:\ken\" & c.Tag & ".pdf"
Worksheets(c.Tag).ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End If
Next i
End Sub

[Hotnumbers]

Kenneth Hobs...


Thank you for the most simple and elegent code...

Also thank you for the explination.