checkboxes, worksheets and arrays

[LJ]

Good Morning Everyone,

I have a userform that has a checkbox for each sheet that the user has the
option to print. Once you select OK, the vba should group the sheets
selected and print. All I have is:

Sub PrintSheets()
'
Sheets(Array("how do I specify which checkboxes were selected")).Select
Sheets("whatever the first sheet in group is called").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Sheet1").Select
End Sub

Any suggestions would be greatly appreciated. I checked the NG but I've
been away from it for a bit and I don't have all the posts anymore - what I
did find wasn't suitable to my problem. I have also checked many reference
books.

Lynn.

[Dave Peterson]

Can I offer an alternative instead of checkboxes? Use a listbox (with boxes in
that if you want).

I had a userform with one listbox and 4 commandbuttons.
The four commandbuttons are ok, cancel, select all, deselect all.

Option Explicit
Private Sub cBtnCancel_Click()
Unload Me
End Sub

Private Sub cBtnSelectAll_Click()
Dim iCtr As Long
For iCtr = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(iCtr) = True
Next
End Sub

Private Sub cBtnDeselectAll_Click()
Dim iCtr As Long
For iCtr = 0 To Me.ListBox1.ListCount - 1
Me.ListBox1.Selected(iCtr) = False
Next
End Sub

Private Sub cBtnOk_Click()
Dim mySheets() As String
Dim iCtr As Long
Dim shCtr As Long

shCtr = 0
For iCtr = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCtr) = True Then
shCtr = shCtr + 1
ReDim Preserve mySheets(1 To shCtr)
mySheets(shCtr) = Me.ListBox1.List(iCtr)
End If
Next

If shCtr > 0 Then
ActiveWorkbook.Sheets(mySheets).PrintOut
Else
MsgBox "Nothing selected"
Exit Sub
End If

Unload Me

End Sub

Private Sub UserForm_Initialize()

Dim iCtr As Long
With Me.ListBox1
.Clear
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
For iCtr = 1 To ActiveWorkbook.Sheets.Count
If ActiveWorkbook.Sheets(iCtr).Visible Then
.AddItem ActiveWorkbook.Sheets(iCtr).Name
End If
Next
End With

End Sub

--

Dave Peterson
ec3...@msn.com

[LJ]

Thank you Dave - excellent alternative.
Lynn.

"Dave Peterson" <ec3...@msn.com> wrote in message
news:3E93883A...@msn.com...