About Us

pfadt_mann

Thank you.

"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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

--

Dave Peterson

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Is It Possible To Dynamically Name Series?	MJ	Charts and Charting in Excel	1	July 29th 08 06:49 PM
Getting handle to Row # dynamically	prakash	Excel Discussion (Misc queries)	1	September 12th 06 08:53 AM
Sum columns dynamically	Delboy	Excel Worksheet Functions	1	July 28th 06 03:33 PM
generate sub dynamically	Christian Galbavy[_2_]	Excel Programming	1	March 11th 05 04:19 PM
Name a range dynamically	Jerry[_12_]	Excel Programming	1	October 8th 03 12:53 PM

Menu

About Us