I would create an array of Boolean values. I would set the array, initially, to the state of the checkboxes, true for Checked and false for unchecked
Dim BoolArray(n) as boolean
' on initialization
for I = 0 to n-1
BoolArray(I) = True
next I
Double-click each checkbox to create a separate "handler" subroutine unique to that checkbox. In the routine, set the BoolArray variable to the state of the checkbox:
Private Sub CheckBox1_Click()
BoolArray(0) = Checkbox1.Checked ' true if checked, false if not
End Sub
If I were doing this in Visual Basic or Visual Basic.NET, I would use the "TAG" property of the checkbox. I would put a unique number in each TAG and use that to set the Boolean array value. I'm not sure if this can be done in VBA, so that's why I'm hedging here. If you can get a reference to the checkbox, you can pick its TAG property and use that as an index into BoolArray. This would allow you to use one "handler" subroutine to fill the BoolArray array. Maybe you can figure that out.
In any case, when you click on a "Proceed" button, or something similar, all you have to do is
For I = 0 to n-1
If BoolArray(I) then
... process here.
end if
Next I
--------------------
In Visual Basic (version 6 or before), they had the concept of "control arrays". I'm not sure if they have that in VBA or not. If they do, it's a simple matter of looping through using the name you gave to the control array and indexing it. Hit KF1> and look up "control arrays".
In Visual Basic.NET, they've thrown out the concept of a control array (I never liked it anyway). What I do now (and, in fact, I do it in older VB programs because I dislike the way control arrays are handled) is create an array of controls:
Dim CkBxArray(n) of CheckBox
then, in an initialization routine, I initialize the array. It's a bit of a pain, but only a tiny pain:
CkBxArray(0) = Checkbox1
CkBxArray(1) = Checkbox2
CkBxArray(2) = Checkbox3
CkBxArray(3) = Checkbox4
CkBxArray(4) = Checkbox5
CkBxArray(5) = Checkbox6
CkBxArray(6) = Checkbox7
...
You can then use CkBxArray to evaluate the .Checked property.
|