Help -- Loop or Array? How to identify?

  • Thread starter Thread starter zSplash
  • Start date Start date
Z

zSplash

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
 
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
(e-mail address removed)
 
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
 
Back
Top