Hello! I have a user form in which I read values from text boxes and check
boxes. I then want to store these values in arrays. My code is now written so
that I give values to the arrays directly. However it would be better to loop
this process, a task that I seem to fail since I do not know how to handle
the reference to the text boxes and check boxes. My code:

checkBoxArray(0) = CheckBox1.Value
checkBoxArray(1) = CheckBox2.Value
checkBoxArray(2) = CheckBox3.Value
checkBoxArray(3) = CheckBox4.Value

I can replace the number refering to the place in the array with a variable
but is it possible to handle the check boxes in the same manner i.e. refer to
the by the usage of a variable? If so is there any function that counts the
number of check boxes in a user form? I am greatful for any assistance that
you can give me.

Dim i As Long
Dim ctl As Control
Dim checkBoxArray

ReDim checkBoxArray(0 To 0)
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
ReDim Preserve checkBoxArray(0 To i)
checkBoxArray(i) = ctl.Value
i = i + 1
End If
Next ctl


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"franzklammer" wrote in message
...
Hello! I have a user form in which I read values from text boxes and check
boxes. I then want to store these values in arrays. My code is now written
so
that I give values to the arrays directly. However it would be better to
loop
this process, a task that I seem to fail since I do not know how to handle
the reference to the text boxes and check boxes. My code:

checkBoxArray(0) = CheckBox1.Value
checkBoxArray(1) = CheckBox2.Value
checkBoxArray(2) = CheckBox3.Value
checkBoxArray(3) = CheckBox4.Value

I can replace the number refering to the place in the array with a
variable
but is it possible to handle the check boxes in the same manner i.e. refer
to
the by the usage of a variable? If so is there any function that counts
the
number of check boxes in a user form? I am greatful for any assistance
that
you can give me.

Thank you very much Bob! The code works great! I have two similar problems.
In the user form I also have text boxes. on the left side of the textboxes
there are start dates that the user is to submit. On the right side there are
end dates that the user also shall submit. I do not want to mix these dates
up but it would be perfect if it was possible to use the same code as you
gave me (with some changes) for setting the dimension of the arrays (I want
one for start dates and one for end dates). Also populating the arrays in the
same manner as before would be superb! Is there any ordering of the placement
of the text boxes that might faciliate this e.g. that the counter starts at
top left and then moves right etc. so that odd numbers would belong to one
array etc.?

"Bob Phillips" skrev:

Dim i As Long
Dim ctl As Control
Dim checkBoxArray

ReDim checkBoxArray(0 To 0)
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
ReDim Preserve checkBoxArray(0 To i)
checkBoxArray(i) = ctl.Value
i = i + 1
End If
Next ctl


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"franzklammer" wrote in message
...
Hello! I have a user form in which I read values from text boxes and check
boxes. I then want to store these values in arrays. My code is now written
so
that I give values to the arrays directly. However it would be better to
loop
this process, a task that I seem to fail since I do not know how to handle
the reference to the text boxes and check boxes. My code:

checkBoxArray(0) = CheckBox1.Value
checkBoxArray(1) = CheckBox2.Value
checkBoxArray(2) = CheckBox3.Value
checkBoxArray(3) = CheckBox4.Value

I can replace the number refering to the place in the array with a
variable
but is it possible to handle the check boxes in the same manner i.e. refer
to
the by the usage of a variable? If so is there any function that counts
the
number of check boxes in a user form? I am greatful for any assistance
that
you can give me.

Mr Klammer,

You could always set the tag properties of the text boxes to Start for start
dates and End for End Dates, and then just use

Dim iStart As Long
Dim iEnd As Long
Dim ctl As Control
Dim textboxStart
Dim textboxEnd

ReDim textboxStart(0 To 0)
ReDim textboxEnd(0 To 0)
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Then
If ctl.Tag = "start" Then
ReDim Preserve textboxStart(0 To iStart)
textboxStart(iStart) = ctl.Value
iStart = iStart + 1
Else
ReDim Preserve textboxEnd(0 To iEnd)
textboxEnd(iEnd) = ctl.Value
iEnd = iEnd + 1
End If
End If
Next ctl



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"franzklammer" wrote in message
...
Thank you very much Bob! The code works great! I have two similar
problems.
In the user form I also have text boxes. on the left side of the textboxes
there are start dates that the user is to submit. On the right side there
are
end dates that the user also shall submit. I do not want to mix these
dates
up but it would be perfect if it was possible to use the same code as you
gave me (with some changes) for setting the dimension of the arrays (I
want
one for start dates and one for end dates). Also populating the arrays in
the
same manner as before would be superb! Is there any ordering of the
placement
of the text boxes that might faciliate this e.g. that the counter starts
at
top left and then moves right etc. so that odd numbers would belong to one
array etc.?

"Bob Phillips" skrev:

Dim i As Long
Dim ctl As Control
Dim checkBoxArray

ReDim checkBoxArray(0 To 0)
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
ReDim Preserve checkBoxArray(0 To i)
checkBoxArray(i) = ctl.Value
i = i + 1
End If
Next ctl


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"franzklammer" wrote in message
...
Hello! I have a user form in which I read values from text boxes and
check
boxes. I then want to store these values in arrays. My code is now
written
so
that I give values to the arrays directly. However it would be better
to
loop
this process, a task that I seem to fail since I do not know how to
handle
the reference to the text boxes and check boxes. My code:

checkBoxArray(0) = CheckBox1.Value
checkBoxArray(1) = CheckBox2.Value
checkBoxArray(2) = CheckBox3.Value
checkBoxArray(3) = CheckBox4.Value

I can replace the number refering to the place in the array with a
variable
but is it possible to handle the check boxes in the same manner i.e.
refer
to
the by the usage of a variable? If so is there any function that
counts
the
number of check boxes in a user form? I am greatful for any assistance
that
you can give me.