VBA - save to pdf via checkbox selection

[Donald1]

Hi Guys,

In need of some assistance! I have a document which is 7 pages. The rows which make up pages 2-7 are hidden as default. Page one is a cover page and will not change.

Pages 2-7 are linked to individual AxtiveX control boxes. So, when a check box is selected the corresponding page will become un-hidden. For example when check box three is checked page three will appear (ie. go from hidden to un-hidden).

I'd like to create a VBA which saves the pages which are visible to the user to pdf. So for example, if the user selects checkbox 2,5,6 - those pages plus the cover page will create a four page pdf document.

My current code is below:

Sub Printsave()
Dim Folder As String
Dim Name As String
Dim Path As String
Dim zone_impression


Folder = ThisWorkbook.Path & "\"
Name = ActiveSheet.Range("G3").Value
Path = Folder & Application.PathSeparator & Name & " - Client Checklist.pdf"


zone_impression = "A1:N172" 'i'd like the range to only be the cells visible to the user (which is created from the check box slections they have made)


Range(zone_impression).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=True, OpenAfterPublish:=True

End Sub


Everything works well apart but when saving to pdf it will create a document with lots of blank pages in between the pages visible to the user from the excel screen.

Are you able to create a pdf document from multiple cell ranges from excel? can anyone help?

Thanks in advance!

 

[yky]

Try this:

[COLOR=#574123]Sub Printsave()[/COLOR]
[COLOR=#574123]Dim Folder As String[/COLOR]
[COLOR=#574123]Dim Name As String[/COLOR]
[COLOR=#574123]Dim Path As String[/COLOR]
[COLOR=#574123]Dim zone_impression[/COLOR]

[COLOR=#574123]Folder = ThisWorkbook.Path & "\"[/COLOR]
[COLOR=#574123]Name = ActiveSheet.Range("G3").Value[/COLOR]
[COLOR=#574123]Path = Folder & Application.PathSeparator & Name & " - Client Checklist.pdf"[/COLOR]

[COLOR=#574123]zone_impression = "A1:N172" 'i'd like the range to only be the cells visible to the user (which is created from the check box slections they have made) [/COLOR]

Sheets(Array(1,2,5,6)).Select
[COLOR=#574123]ActiveSheet.Range(zone_impression).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path, _[/COLOR]
[COLOR=#574123]Quality:=xlQualityStandard, IncludeDocProperties:=True, _[/COLOR]
[COLOR=#574123]IgnorePrintAreas:=False, OpenAfterPublish:=True[/COLOR]

[COLOR=#574123]End Sub[/COLOR]

 

[Donald1]

Hi yky, Thanks for your suggestion. Here is the code which does what i'm after:

Sub v3test()
Dim cheboxs()
Dim cheboxzones()
 
Dim Folder As String
Dim Name As String
Dim Path As String
Dim printzone
 
printzone = " B1: N53 "
cheboxs = Array(Sheet1.CheckBox1, Sheet1.CheckBox2, Sheet1.CheckBox3, Sheet1.CheckBox4, Sheet1.CheckBox5, Sheet1.CheckBox6, Sheet1.CheckBox7)
cheboxzones = Array(" B54:N135 ", " B136:N217 ", " B218:N299 ", " B300:N381 ", " B382:N463 ", " B464:N545 ", " B546:N627 ")
 
 
For i = 0 To UBound(cheboxs)
If cheboxs(i) = True Then
printzone = printzone & cheboxzones(i)
End If
Next
If Not printzone = "" Then
printzone = Replace(printzone, "  ", ", ", 1, -1, 1)
'Range(printzone).PrintOut
'the rows with form a page must be adjusted to have the same size of eath page of pdf
Folder = ThisWorkbook.Path & "\"
       Name = ActiveSheet.Range("G3").Value
        Path = Folder & Application.PathSeparator & Name & " - Client Checklist.pdf"
       Range(printzone).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=True, OpenAfterPublish:=True
 
End If
End Sub

 

[Abhiii_reet]

Hi yky, Thanks for your suggestion. Here is the code which does what i'm after:

Sub v3test()
Dim cheboxs()
Dim cheboxzones()
 
Dim Folder As String
Dim Name As String
Dim Path As String
Dim printzone
 
printzone = " B1: N53 "
cheboxs = Array(Sheet1.CheckBox1, Sheet1.CheckBox2, Sheet1.CheckBox3, Sheet1.CheckBox4, Sheet1.CheckBox5, Sheet1.CheckBox6, Sheet1.CheckBox7)
cheboxzones = Array(" B54:N135 ", " B136:N217 ", " B218:N299 ", " B300:N381 ", " B382:N463 ", " B464:N545 ", " B546:N627 ")
 
 
For i = 0 To UBound(cheboxs)
If cheboxs(i) = True Then
printzone = printzone & cheboxzones(i)
End If
Next
If Not printzone = "" Then
printzone = Replace(printzone, "  ", ", ", 1, -1, 1)
'Range(printzone).PrintOut
'the rows with form a page must be adjusted to have the same size of eath page of pdf
Folder = ThisWorkbook.Path & "\"
       Name = ActiveSheet.Range("G3").Value
        Path = Folder & Application.PathSeparator & Name & " - Client Checklist.pdf"
       Range(printzone).ExportAsFixedFormat Type:=xlTypePDF, Filename:=Path, _
      Quality:=xlQualityStandard, IncludeDocProperties:=True, _
     IgnorePrintAreas:=True, OpenAfterPublish:=True
 
End If
End Sub

Hii Boss,

I'm facing some problem if i'm writing below code for create pdf from multiple sheet.

cheboxzones = Array(" Sheet2!B54:N135 ", " Sheet3!B136:N217 ", " Sheet4!B218:N299 ", " Sheet5!B300:N381 ", " B382:N463 ", " B464:N545 ", " B546:N627 ")

this code work on only 1 checkbox not working on multiple selection.

kindly resolve this if you know about this error.

Regards
Abhishek Singh