How to dynamically modify unserforms

[pfadt_mann]

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]

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]

Thank you Dave, this is a nice option. I've never used listboxes like this.

Is there anyway to add the listbox dynamically? In short, could I use the
form for printing, as described below, one time then to display a message
another time, or add a combobox or textbox to retrieve user input. Or do I
need to create a userform for each item I need.

There are two reasons I want to use one user form for many things. First, I
put my company's logo on the userform with company colors. Second, this
document will be emailed at times and I need to keep the size reasonable.


Pfadt

 

[Dave Peterson]

If the userforms are significantly different, I'd just use different
userforms--but within the same workbook/addin.

If the userforms are very close, I wouldn't add the controls during run time.
I'd add all the controls where I wanted them, but hide or show them based on
what I need.

You may even want to consider using a multipage userform -- kind of like the
Tools|Options dialog in xl2003. Each page would be for a different purpose????

 

[pfadt_mann]

Thank you.

If the userforms are significantly different, I'd just use different
userforms--but within the same workbook/addin.

If the userforms are very close, I wouldn't add the controls during run
time.
I'd add all the controls where I wanted them, but hide or show them based
on
what I need.

You may even want to consider using a multipage userform -- kind of like
the
Tools|Options dialog in xl2003. Each page would be for a different
purpose????