Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
1 Dynamic Userform vs Multiple Userforms
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
1 Dynamic Userform vs Multiple Userforms
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 On Apr 20, 12:44 pm, ExcelMonkey wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
1 Dynamic Userform vs Multiple Userforms
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 "Susan" wrote: 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 On Apr 20, 12:44 pm, ExcelMonkey wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can a Combobox in a Userform access multiple dynamic tables | Excel Discussion (Misc queries) | |||
Multiple Userforms Upon Click | Excel Programming | |||
Multiple Controls in a Dynamic UserForm | Excel Programming | |||
Multiple Userforms at once | Excel Programming | |||
dynamic userforms | Excel Programming |