Printout sheets with a userform

[fragher75]

Hi,
I would printout some sheets with a user form. In this userform I put
checkboxes near the names of sheets, so when I select one or more
chechboxes I can printout (or preview) the sheet(s) selected.
If I record a macro, the multiselection of sheets is:

Sheets(Array("Sheet1", "Sheet2",
"Sheet3","Sheet4","Sheet5","Sheet6")).Select

but I have more combination of choose...

Can you help me?
Thanks

 

[Tom Ogilvy]

Assume your have 10 checkboxes names checkbox1 to checkbox10 and 10 labels
with captions corresponding to the sheets and named label1 to label10 as an
example.

Dim i as Long, j as Long
Dim v as Variant
redim v(0 to )
j = 0
for i = 1 to 10
if Userform1.Controls("Checkbox" & i).Value then
redim preserve v(0,j)
v(j) = Userform1.Controls("Label" & i).Caption
end if
Next
sheets(v).Select


Adapt to fit your actual situation.

 

[fragher75]

Thanx Tom,
but there's an error in your code.
I don't know very well the use of "redim" but you wrote "redim v(0 to
)".
What do I write ?
The debugger finds an error in "redim preserve v(0,j)" too.
Are you sure that it's right.
Thanx
Francesco.

 

[Tom Ogilvy]

Couple of typos in that. The missing zero was in the other line that showed
an error and I didn't increment J, This is tested and worked fine for me
with the assumptions I stated.

Private Sub CommandButton1_Click()
Dim i As Long, j As Long
Dim v As Variant
ReDim v(0 To 0)
j = 0
For i = 1 To 5
If UserForm1.Controls("Checkbox" & i).Value Then
ReDim Preserve v(j)
v(j) = UserForm1.Controls("Label" & i).Caption
j = j + 1
End If
Next
Sheets(v).Select

End Sub

 

[davesexcel]

I like this code as well



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

 

[Tom Ogilvy]

Why not give credit to John Walkenbach and link to the original source of
the code.

http://www.j-walk.com/ss/excel/tips/tip48.htm

although Bob Phillips has used it extensively (posted/web site) as well so
you could have ripped him.