Results 1 to 4 of 4

VBA code to print selected worksheets

This is a discussion on VBA code to print selected worksheets within the Excel Questions forums, part of the Question Forums category; I used the code from here and encountered some issues. The first problem is the last worksheet becomes the active ...

  1. #1
    New Member
    Join Date
    Feb 2010
    Posts
    5

    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
    Application.Dialogs(xlDialogPrinterSetup).Show

    '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 = _
    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
    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
    CurrentSheet.Activate

    End Sub

  2. #2
    New Member
    Join Date
    Feb 2010
    Posts
    5

    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
        Application.Dialogs(xlDialogPrinterSetup).Show
    '   
    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
            MsgBox 
    "Workbook is protected."vbCritical
            
    Exit Sub
        End 
    If
    '   Add a temporary dialog sheet
        Set CurrentSheet = ActiveSheet
        x = CurrentSheet.Name
        Set PrintDlg = ActiveWorkbook.DialogSheets.Add
        SheetCount = 0
    '   
    Add the checkboxes
        TopPos 
    40
        
    For 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
    '   
    Get the number of print copies for each report
        Numcop 
    Application.InputBox("Enter number of copies to print:"_
        
    "How Many Copies?"1Type:=1)
        If 
    Numcop 0 Then
        
    ElseIf Len(Numcop) > 0 Then
            End 
    If
    '   Display the dialog box
        CurrentSheet.Activate
        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
                    Else
                        Worksheets(cb.Caption).Select Replace:=False '
    Activate
                    End 
    If
                        
    cnt cnt 1
                    End 
    If
                    
    Next cb
                        ActiveWindow
    .SelectedSheets.PrintOut copies:=Numcop
                        
    'ActiveSheet.PrintPreview 'for debugging
            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
    (x).Select
    End Sub 

  3. #3
    New Member
    Join Date
    Feb 2010
    Posts
    5

    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
    New Member
    Join Date
    Oct 2014
    Posts
    1

    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
  •  


DMCA.com