Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help -- Loop or Array? How to identify?
I have a userform with checkboxes. As each checkbox is selected, a
secondary form shows, which has checkboxes and option buttons. After gathering input from these controls, various things are done. I wonder what is the best way to "capture" the values from the various sub forms, checkboxes, and optionbox groups. I have considered using an array(x=1 to 8,y=1 to 5,z=1 to 5), where x would represent the subforms, y the checkboxes, and z the option buttons, but need direction. I thought using a public array variable would make the values easily "retrievable" through all modules.I thought about assigning a value to one cell of the three-dimenstional array a value when the control was true, but don't know how to refer to the controls by name. I also have considered just looping through the various forms on the controls, but is there a efficient way to loop through all the checkboxes and option buttons for the various subforms? With hopes of easy looping, I have prefaced the names of all the sub userforms with "ufm1Blah...ufm8Bleh", the checkboxes with "ckb1Name...ckb5Name", and the option buttons with "opt1One...opt5Five". Can anyone give me a kick? I think I should be looping through all the controls of the project (and then consider the forms that have left(asdf,4)= ufm or ckb or opt, but I'm stuck. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help -- Loop or Array? How to identify?
First of all, I recommend using similar names for the check boxes on all 3 user forms
For example, you got 5 check boxes on each of the 3 user forms. (The naming convention of the checkboxes should be the same. If the checkboxes on form1 are called check1, check2, check3; the checkboxes on form2 should best use the same naming convention. So, if the check boxes are called check1, check2, check3, check1, check And the UserForms are called : form1, form2, form3 You may systematically set up 3 FOR-NEXT loop to collect the information Option base 'this statement ensures that the lower bound of an array has the index number 1 instead of Dim tmp as intege Dim a(5), b(5), c(5 For tmp = 1 to a(tmp) = form1.controls("check" & tmp).valu Nex For tmp = 1 to b(tmp) = form2.controls("check" & tmp).valu Nex For tmp = 1 to c(tmp) = form3.controls("check" & tmp).valu Nex Regards Edwin Ta |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help -- Loop or Array? How to identify?
If the forms, checkboxes, etc were numbered in an orderly
fashion, e.g frm1 to frm8, ckb1 to ckb5 then you could loop Dim i As Integer, n As Integer Dim isChecked(1 To 8, 1 To 5) As Boolean For i = 1 To 8 For n = 1 To 5 isChecked(i, n) = UserForms("frm" & i).Controls("ckb" & n).Value Next n Next i If naming is not so straightforward then Dim frm As UserForm Dim ctrl As ctrl For Each frm In UserForms If Instr(1, frm.Name, "frm", vbTextCompare) 0 Then i = Val(Mid(frm.Name, 4,1)) For Each ctrl in frm.Controls If Instr(1, ctrl.Name, "ckb", vbTextCompare) 0 Then n = Val(Mid(ctrl.Name, 4, 1)) isChecked(i, n) = ctrl.Value ElseIf Instr(1, ctrl.Name, "opt", vbTextCompare) 0 Then : : End If Next ctrl End If Next frm On a more purist note, only use a three dimensional matrix if there are 5 option buttons per check box Kevin Beckham -----Original Message----- I have a userform with checkboxes. As each checkbox is selected, a secondary form shows, which has checkboxes and option buttons. After gathering input from these controls, various things are done. I wonder what is the best way to "capture" the values from the various sub forms, checkboxes, and optionbox groups. I have considered using an array(x=1 to 8,y=1 to 5,z=1 to 5), where x would represent the subforms, y the checkboxes, and z the option buttons, but need direction. I thought using a public array variable would make the values easily "retrievable" through all modules.I thought about assigning a value to one cell of the three-dimenstional array a value when the control was true, but don't know how to refer to the controls by name. I also have considered just looping through the various forms on the controls, but is there a efficient way to loop through all the checkboxes and option buttons for the various subforms? With hopes of easy looping, I have prefaced the names of all the sub userforms with "ufm1Blah...ufm8Bleh", the checkboxes with "ckb1Name...ckb5Name", and the option buttons with "opt1One...opt5Five". Can anyone give me a kick? I think I should be looping through all the controls of the project (and then consider the forms that have left(asdf,4)= ufm or ckb or opt, but I'm stuck. TIA . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula to Identify Leading Zeros. | Excel Discussion (Misc queries) | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
How do identify if a number is NOT in an array? | Excel Discussion (Misc queries) | |||
Identify occurence in "array" which meets condition(s) | Excel Discussion (Misc queries) | |||
Can you identify the maximum value in an array? | Excel Programming |