VBA code to print selected worksheets

M

majopa

New Member
Joined
Feb 2, 2010
Messages
5
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
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: 
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 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
'   Get the number of print copies for each report
    Numcop = Application.InputBox("Enter number of copies to print:", _
    "How Many Copies?", 1, Type:=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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question Question
Is rich text available for DialogSheet
Replies
2
Views
88
Formula11
F
P
  • Question Question
Adding Sort To Current Code
2
Replies
14
Views
245
Pacman52
P
A
  • Question Question
Excel VB Organogram
Replies
1
Views
301
anuradhagrewal
A
4
  • Question Question
VBA to Print Labels but Issues Selecting Printer
Replies
1
Views
296
rpaulson
rpaulson
H
  • Question Question
Macro to select File to Print
Replies
0
Views
373
howard
H

Forum statistics

Threads
1,224,582
Messages
6,179,670
Members
452,936
Latest member
anamikabhargaw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top