Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print multiple sheets from checkboxes into one pdf
I have been searching for a way to be able to select certain sheets from a
workbook and print into one pdf file. I have two codes that I found through searching but cannot figure out how to combine them. I have very little programing knowledge so please be gentile. I am using Excell 2003 & CutePDF. Each of the codes have been placed into seperate command buttons. One code brings up a dialog box that allows me to check the sheets I want to print. The other will print all sheets in a workbook into one pdf file. Here are the two codes: Private Sub CommandButton1_Click() Dim i As Integer Dim TopPos As Integer Dim SheetCount As Integer Dim PrintDlg As DialogSheet Dim CurrentSheet As Worksheet Dim cb As CheckBox Application.ScreenUpdating = False ' Check for protected workbook If ActiveWorkbook.ProtectStructure Then MsgBox "Workbook is protected.", vbCritical Exit Sub End If ' Add a temporary dialog sheet Set CurrentSheet = ActiveSheet Set PrintDlg = ActiveWorkbook.DialogSheets.Add SheetCount = 0 ' Add the checkboxes TopPos = 40 For i = 1 To ActiveWorkbook.Worksheets.Count Set CurrentSheet = ActiveWorkbook.Worksheets(i) ' Skip empty sheets and hidden sheets If Application.CountA(CurrentSheet.Cells) < 0 And _ CurrentSheet.Visible Then SheetCount = SheetCount + 1 PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5 PrintDlg.CheckBoxes(SheetCount).Text = _ CurrentSheet.Name TopPos = TopPos + 13 End If Next i ' Move the OK and Cancel buttons PrintDlg.Buttons.Left = 240 ' Set dialog height, width, and caption With PrintDlg.DialogFrame .Height = Application.Max _ (68, PrintDlg.DialogFrame.Top + TopPos - 34) .Width = 230 .Caption = "Select sheets to print" End With ' Change tab order of OK and Cancel buttons ' so the 1st option button will have the focus PrintDlg.Buttons("Button 2").BringToFront PrintDlg.Buttons("Button 3").BringToFront ' Display the dialog box CurrentSheet.Activate Application.ScreenUpdating = True If SheetCount < 0 Then If PrintDlg.Show Then Application.Dialogs(xlDialogPrinterSetup).Show For Each cb In PrintDlg.CheckBoxes If cb.Value = xlOn Then Worksheets(cb.Caption).Activate ActiveSheet.PrintOut ' ActiveSheet.PrintPreview 'for debugging End If Next cb End If Else MsgBox "All worksheets are empty." End If ' Delete temporary dialog sheet (without a warning) Application.DisplayAlerts = False PrintDlg.Delete ' Reactivate original sheet Sheets("Info").Activate End Sub Private Sub CommandButton2_Click() Dim sht Application.ScreenUpdating = False For Each sht In Sheets If sht.Visible Then sht.Select Replace:=False Next ActiveWindow.SelectedSheets.PrintOut copies:=1 ActiveSheet.Select Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Print area multiple sheets | Excel Programming | |||
Using checkboxes to print multiple worksheets | Excel Discussion (Misc queries) | |||
Print Multiple Sheets to PDF Problem | Excel Discussion (Misc queries) | |||
Print multiple sheets | Excel Programming | |||
Print Multiple Sheets | Excel Programming |