Results 1 to 4 of 4

Thread: VBA code to print selected worksheets

  1. #1
    New Member
    Join Date
    Feb 2010

    Default VBA code to print selected worksheets

    I used the code from here and encountered some issues. The first problem is the last worksheet becomes the active worksheet regardless of which sheet the user was on. In addition, the last worksheet is always printed, even if it was not selected. It appears there is a fix for this in this post that I did add to but figured I should start a new post so I apologize for the duplicate. However, I am unable to incorporate it so that it works completely. I have also added a 'Printer Setup' and a 'Number of Copies' dialog boxes (they probably need to be placed elsewhere in the code). The two additions I would like to make is to have the ability to select all 'Checkboxes' at one time and to have all of the pages print sequentially. I have included my best attempt (some sections may be duplicated/commented out as I tried to correct), but I am in no way a programmer. Any help is appreciated. Thank you.

    Private Sub CommandButton1_Click()

    'Display "Printer Setup" dialog box

    'Option Explicit

    'Sub SelectSheets()
    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 = _
    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
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
    If PrintDlg.Show Then
    For Each cb In PrintDlg.CheckBoxes
    If cb.Value = xlOn Then
    ' ActiveSheet.PrintPreview 'for debugging
    End If
    Next cb
    End If
    MsgBox "All worksheets are empty."
    End If

    ' Delete temporary dialog sheet (without a warning)
    Application.DisplayAlerts = False

    ' Reactivate original sheet

    End Sub

  2. #2
    New Member
    Join Date
    Feb 2010

    Default Re: VBA code to print selected worksheets

    I have had some help with modifications and the new code looks like this. Now I am trying to add a 'Select All' button to the dialog box.

    Thanks in advance.

    PHP Code:
    Private Sub CommandButton1_Click()
    '   Display "Printer Setup" dialog box
    Option Explicit
    '   Sub SelectSheets()
        Dim i As Integer
        Dim TopPos As Integer
        Dim SheetCount As Integer
        Dim PrintDlg As DialogSheet
        Dim CurrentSheet As Worksheet
        Dim cb As CheckBox
        Dim Numcop As Long
        Application.ScreenUpdating = False
    Check for protected workbook
    If ActiveWorkbook.ProtectStructure Then
    "Workbook is protected."vbCritical
    Exit Sub
    '   Add a temporary dialog sheet
        Set CurrentSheet = ActiveSheet
        x = CurrentSheet.Name
        Set PrintDlg = ActiveWorkbook.DialogSheets.Add
        SheetCount = 0
    Add the checkboxes
    For 1 To ActiveWorkbook.Worksheets.Count
            Set CurrentSheet 
    '       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 = _
                TopPos = TopPos + 13
            End If
        Next i
    Move the OK and Cancel buttons
    .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
    Get the number of print copies for each report
    Application.InputBox("Enter number of copies to print:"_
    "How Many Copies?"1Type:=1)
    Numcop 0 Then
    ElseIf Len(Numcop) > 0 Then
    '   Display the dialog box
        Dim cnt As Integer
        Application.ScreenUpdating = True
        If SheetCount <> 0 Then
            If PrintDlg.Show Then
                For Each cb In PrintDlg.CheckBoxes
                    If cb.Value = xlOn Then
                        If cnt = 0 Then
                        Worksheets(cb.Caption).Select ' 
    Replace:=False 'Activate
                        Worksheets(cb.Caption).Select Replace:=False '
    cnt cnt 1
    Next cb
    .SelectedSheets.PrintOut copies:=Numcop
    'ActiveSheet.PrintPreview 'for debugging
    MsgBox "All worksheets are empty."
    End If
    '   Delete temporary dialog sheet (without a warning)
        Application.DisplayAlerts = False
    Reactivate original sheet
    End Sub 

  3. #3
    New Member
    Join Date
    Feb 2010

    Default Re: VBA code to print selected worksheets

    I was hoping that someone may be able to help me add a 'Select All' button to the dialog box that allows the user to select the sheets to print.

    Thank you in advance.

  4. #4

    Default Re: VBA code to print selected worksheets

    Hi, I found your code very useful and helpful. Is there any way to exclude certain sheets from being displayed in the message box? I would like to list a few by name so that there is not option to print those sheets, I have tried = xlHidden and not had any luck.

    Thanks, I apreciate your help in advance.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts