Printout sheets with a userform

  • Thread starter Thread starter fragher75
  • Start date Start date
F

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
 
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.
 
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.
 
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
 
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
 
Back
Top