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
.
|