Print selected sheets in Excel

[M H]

I have to create a userform with several checkboxes to select particular
sheets for printing. Check one box will select one sheet. A "Print"
button is created with the code:

Private Sub CommandButton1_Click()

ActiveWorkbook.SelectedSheets.PrintOut Copies:=1
Unload Me

End Sub

Please advice how to pass the checked sheets for printing. Thanks.

 

[Ron de Bruin]

Hi M H

Try this

Add a listbox and a button on a test userform
In the properties of the listbox set Multiselect to 1

Add this code in the Userform module

Private Sub CommandButton1_Click()
Dim arr() As String
Dim N As Integer
N = 0
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then
N = N + 1
ReDim Preserve arr(1 To N)
arr(N) = ListBox1.List(i)
End If
Next i
If N = 0 Then
MsgBox "You must select at least one Sheet"
Exit Sub
End If
ThisWorkbook.Worksheets(arr).PrintOut
Unload Me
End Sub


Private Sub UserForm_Initialize()
For Each ws In ThisWorkbook.Sheets
If ws.Visible = True Then
Me.ListBox1.AddItem (ws.Name)
End If
Next
End Sub


Here is a small example workbook
http://www.rondebruin.com/Printselectedsheets.zip

 

[Sharad Naik]

I assume that the caption of each of the check box is name of a sheet.
Add a frame in your useform and move all the check boxes in to the frame.
I assume that the name of the frame is Frame1
Then you can use below code:

Private Sub CommandButton1_Click()
Dim c, sh
For Each c In Frame1.Controls
For Each sh In ThisWorkbook.Sheets
If Trim(c.Caption) = sh.Name Then
If c.Value Then
sh.PrintOut
End If
End If
Next sh
Next c
End Sub


Sharad

 

[Tom Ogilvy]

Here is some code from John Walkenbach's site. I would just post a link,
but the site seems to be down right now: This code uses dialogsheets, but
they are still supported, so no reason not to use them.

http://www.j-walk.com/ss/excel/tips/tip48.htm
Displaying a Menu of Worksheets to Print

Sub SelectSheetsforPrinting()
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


' 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

 

[M H]

Thanks, Tom, and actually also to J-Walk. I may not like such a long
list of lines but at least I learn how to use dialogsheets.

 

[M H]

Hi Sharad,

Nice and compact codes! The Frame option is magic. Thanks a lot!

MH

 

[M H]

Hi Ron,

Though yours is not what I expected, it works great. Just my fault not
to include in my question that the workbook I work on has a lot of other
sheets and I don't want them in the listbox as your code would do so.
And thanks anyway to show me another example of the usage of arrays, the
weakest part of me!

rgds,

MH