Instead of using checkboxes, you could use a listbox and make that listbox's
style show checkboxes.

I built a small userform (a listbox and two commandbuttons).

This is the code behind the userform:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long
Dim sCtr As Long
Dim mySheetNames() As String

ReDim mySheetNames(1 To ActiveWorkbook.Sheets.Count)
sCtr = 0
With Me.ListBox1
For iCtr = 1 To .ListCount
If .Selected(iCtr - 1) Then
sCtr = sCtr + 1
mySheetNames(sCtr) = .List(iCtr - 1)
End If
Next iCtr
End With

If iCtr = 0 Then
MsgBox "No Sheets Selected"
Else
ReDim Preserve mySheetNames(1 To sCtr)
Me.Hide
ActiveWorkbook.Sheets(mySheetNames).PrintOut preview:=True
End If

Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim iCtr As Long

With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption
End With

For iCtr = 1 To ActiveWorkbook.Sheets.Count
If Sheets(iCtr).Visible = xlSheetVisible Then
Me.ListBox1.AddItem Sheets(iCtr).Name
End If
Next iCtr

With Me.CommandButton1
.Caption = "Cancel"
.Cancel = True
End With

With Me.CommandButton2
.Caption = "Ok"
.Default = True
End With

Me.Caption = "Select Sheets to Print"

End Sub



pfadt_mann wrote:

I am setting up a macro to allow the user to print any or all sheets in a
workbook. I obtained a routine from a website that works great, only now I
would like to use a useform with a specific design (look and feel). I
created a generic userform and am trying to modify the macro to add
checkboxes and other controls dynamically.

The original macro uses dialogsheets but this limits me to generic MS look
and feel. I have looked through the MS Excel help and the web but can't
seem to find anything to help.

My goal is to list each available sheet in a workbook with a checkbox, or
other method to allow the user to select which sheets they wouold like to
have printed.

Any ideas?

Pfadt

--

Dave Peterson