Looping through controls on form

Good Morning,

I have 6 checkboxes grouped (grpMaterials) within a frame on a form. If the
value of a checkbox is true, the following happens within a certain range of
cells on my worksheet:

If chkTextbook.Value = True Then
.Offset(0, 26).Value = Chr(82)
End If

The value of the second checkbox would appear in rng.Offset(0, 27), the
third in rng.offset(0, 28), etc. If the value of the checkbox is false, the
corresponding cell would be blank.

How would I loop through the controls instead of having to write the above
code for each of the checkboxes?

Thank you much,

Robbyn

Dim ctl As msforms.Control
Dim i As Long

i = 25
For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
i = i + 1
If ctl.Value Then
.Offset(0, i).Value = Chr(82)
End If
End If
Next ctl


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
news:C58B60F8-B0CB-436C-A5D7-245D6360BDDE@microsoft.com...
> Good Morning,
>
> I have 6 checkboxes grouped (grpMaterials) within a frame on a form. If
the
> value of a checkbox is true, the following happens within a certain range
of
> cells on my worksheet:
>
> If chkTextbook.Value = True Then
> .Offset(0, 26).Value = Chr(82)
> End If
>
> The value of the second checkbox would appear in rng.Offset(0, 27), the
> third in rng.offset(0, 28), etc. If the value of the checkbox is false,
the
> corresponding cell would be blank.
>
> How would I loop through the controls instead of having to write the above
> code for each of the checkboxes?
>
> Thank you much,
>
> Robbyn
>
>

Thanks Bob! Worked perfectly!

"Bob Phillips" wrote:

>
> Dim ctl As msforms.Control
> Dim i As Long
>
> i = 25
> For Each ctl In Me.Controls
> If TypeName(ctl) = "CheckBox" Then
> i = i + 1
> If ctl.Value Then
> .Offset(0, i).Value = Chr(82)
> End If
> End If
> Next ctl
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Robbyn" <Robbyn@discussions.microsoft.com> wrote in message
> news:C58B60F8-B0CB-436C-A5D7-245D6360BDDE@microsoft.com...
> > Good Morning,
> >
> > I have 6 checkboxes grouped (grpMaterials) within a frame on a form. If
> the
> > value of a checkbox is true, the following happens within a certain range
> of
> > cells on my worksheet:
> >
> > If chkTextbook.Value = True Then
> > .Offset(0, 26).Value = Chr(82)
> > End If
> >
> > The value of the second checkbox would appear in rng.Offset(0, 27), the
> > third in rng.offset(0, 28), etc. If the value of the checkbox is false,
> the
> > corresponding cell would be blank.
> >
> > How would I loop through the controls instead of having to write the above
> > code for each of the checkboxes?
> >
> > Thank you much,
> >
> > Robbyn
> >
> >
>
>
>