Print multiple worksheets to one document

[hergy]

Hello there,


I setup a vba code to print certain worksheets to one document, but does not work.


Description:


My file consist of 5 worksheets. In each worksheet, i defined print area.


I also put specific sheet named "Printout". In "Printout" sheet, i lined up the 5 worksheets above by placing Checkbox field next (Activex controle), so user can select worksheet he wants to print. Then, user enters in TexBox (Activex controle) a page number that will be insert at the first page of the document. Finally, user clicks on the "Print" button (Activex Controle). After clicking on "Print" Button, a dialog box displays to allow user to choose a printer; user chooses a printer and clicks on Ok button of dialog box, the printout is launched.




The vba code for above is as following (code inserted in the sheet "Printout"):

 Private Sub PrintoutPageSetup(Feuille, UneOrientation)
'Page setup before printing a worksheet
'UneOrientation=0 do nothing
'UneOrientation=1 force to  landscape
'UneOrientation=2 force to portrait
    On Error Resume Next
    
    Worksheets.Item(Feuille).PageSetup.RightHeader = ""
    Worksheets.Item(Feuille).PageSetup.LeftHeader = "" + Chr(10) & "&G"
    Worksheets.Item(Feuille).PageSetup.CenterFooter = ""
    Worksheets.Item(Feuille).PageSetup.RightFooter = "&P"
    Worksheets.Item(Feuille).PageSetup.LeftFooter = ""
    Worksheets.Item(Feuille).PageSetup.FirstPageNumber = TextBoxNumeroPage.Value
    Worksheets.Item(Feuille).PageSetup.Zoom = False
    If UneOrientation = 1 Then
        Worksheets.Item(Feuille).PageSetup.Orientation = xlLandscape
    End If
    If UneOrientation = 2 Then
        Worksheets.Item(Feuille).PageSetup.Orientation = xlPortrait
    End If
End Sub




Public Sub Printareapage(Feuille, PlageStr As String, Orientation)
'Print an area in a worksheet
    Dim Plage As Range
    
    Call PrintoutPageSetup(Feuille, Orientation)
    NumeroPage = NumeroPage + 1
    Set Plage = Worksheets.Item(Feuille).Range(PlageStr)
    Plage.PrintOut
End Sub




Private Sub CheckBoxReport_Click()
    If CheckBoxReport.Value Then
        CheckBox1.Value = True
        CheckBox2.Value = True
        CheckBox3.Value = True
        CheckBox4.Value = True
        CheckBox5.Value = True
    Else
        CheckBox1.Value = False
        CheckBox2.Value = False
        CheckBox3.Value = False
        CheckBox4.Value = False
        CheckBox5.Value = False
     End If


End Sub




Private Sub PrintButton_Click()
     
    Application.Dialogs(xlDialogPrinterSetup).Show
    
    Application.StatusBar = "Printout in progress..."
    NumeroPage = TextBoxNumeroPage
      


    If CheckBox1.Value = True Then
        Call Printareapage(("Report_1"), "$A$1:$N$90", 0)
    End If
    If CheckBox2.Value = True Then
        Call Printareapage(("Report_2"), "$A$1:$N$90", 0)
    End If
    If CheckBox3.Value = True Then
        Call Printareapage(("Report_3"), "$A$1:$N$90", 0)
    End If
    If CheckBox4.Value = True Then
        Call Printareapage(("Report_4"), "$A$1:$N$90", 0)
    End If
    If CheckBox5.Value = True Then
        Call Printareapage(("Report_5"), "$A$1:$N$90", 0)
    End If
    
    
    Worksheets.Item("Printout").Activate
    Application.StatusBar = ""
End Sub

I tried to print 2 worksheets with PDFCreator, but it's petering out:


1- I get 2 PDF of one page instead of one PDF of two pages.
2- The page numbers do not incremente. Each page has the same number as entered by user before (the consequence of the issue 1 above).
3- The printout is launched even if i click on the button "Cancel" in dialog box.


Help please.


PS: there is a mean to display an alert message when user clicks on Print button without choosing any worksheet first?