1 Dynamic Userform vs Multiple Userforms

[ExcelMonkey]

I need some advice on this task.

I have a listbox with 21 items. When I click onto an item, I want a unique
userform to display - for that item - with additional parameters for the user
to click. So each item brings up its own additional userform. Now I am
assuming I can approach this two ways:

1) Build 21 additional userforms programmatically
2) Build one very large form with all the controls that is called each time
an item is clicked and will hide unwanted controls.

Assuming option 2 is the way to go, do I have to create 21 click events for
each item in the listbox? Or can this be accomplished using a class module?
Each listbox item will have a unique number an type of controls. So I am not
sure if a class module works. So assuming I create 21 click events, I am
assuming that I will have to build a function which makes the appropriate
changes to the userform before it is loaded. However I will need the option
to pass the unique number of controls to this function.

Function Design_Userform(Optional A as Contorl, Optional B as Control,
Optional C As Control, Optional D as Control, Optional E as Control)
For each cntrl in Controls
'Code to hide the controls that are not needed based on
'IF stmt which hides all controls unless they are the ones passed
'to this function
Next
'Code to place controls where needed. Assume they will simply
'run vertically down form

'Code to size form to accomodate contrls placed
End

And then lastly I am assuming that I have to pass all control box data to
public variables as I need to retain the values while the user finishings
calling up the userform for the remaining listbox items. So there will be an
"OK" button on the additonal userform to pass this data to the public
variables.

Any ideas on how to get this going?

Thanks

EM

 

[Susan]

ExcelMonkey:
(love that name) :^)
look @ using multipage form.
yes, it makes for lots of checking & "if" coding but you end up with
one userform.

(off the top of my head - syntax is probably not correct)
if chkFirst.value = true then
multipage.value=3 'go to another page of the multipage
txtFirst.set focus

when i've done this in the past i've made the other pages invisible
when loading the userform (in the initialization sub).

so it would add

multipage3.visible=true

to the value.

below is some sample coding (that works) from one of my macros
xxxxxxxxxxxxxxxxxxxx
If chkBoolean.Value = True And _
(optCouponsOnly.Value = True Or _
optSchedCoupons.Value = True) Then
MultiPage1.pgCoupons.Visible = True
chkBoolean.Value = False
MultiPage1.Value = 1
txtProgID.SetFocus
MsgBox "Please also fill in these fields, then press
""Continue""." _
, vbInformation, "Let's see................."
Exit Sub
chkBoolean.Value = False
End If
xxxxxxxxxxxxxxxxxxxxxxx

hope this helps!
susan

 

[ExcelMonkey]

Yes can work too. I guess you are only contrained by the abilitiy to add
pages to the multisheet control. I created a Userform2 with listbox1 which
added 21 items to it. I also created a Userform2 which had a multipage
contorl which has 21 pages in it. I then triggered a macro which deleted
unwanted pages on the multipage control based on the item click in the
listbox.

Sub Thing()
UserForm2.Show
End Sub


Private Sub ListBox1_Click()
PageRetain (ListBox1.Value)
UserForm1.Show
End Sub
Private Sub UserForm_Initialize()
Dim tabcount As Integer
Dim X As Integer

tabcount = UserForm1.MultiPage1.Pages.Count

With ListBox1
For X = 1 To tabcount
.AddItem X
Next
End With
End Sub

Function PageRetain(A As Integer)
For Each pg In UserForm1.MultiPage1.Pages
If pg.Index <> A - 1 Then
pg.Visible = False
End If
Next
End Function

This definately is an option.

Thanks

EM