Stuffing Multiple Checkbox Captions

I have a user form with 20 check boxes. I want to loop through them stuffing
the captions with various text retrieved from a worksheet.

The checkboxes are consecutively named checkbox1 to checkbox 20.

for i = 1 to 20 step 1

what goes here?

next i

Thanks,

Sandy

Sandy

For i = 1 To 2
Me.Controls("CheckBox" & i).Caption = "Test" & i
Next i

hth,

Doug

"Sandy" <[email protected]> wrote in message
news:[email protected]...
>I have a user form with 20 check boxes. I want to loop through them
>stuffing
> the captions with various text retrieved from a worksheet.
>
> The checkboxes are consecutively named checkbox1 to checkbox 20.
>
> for i = 1 to 20 step 1
>
> what goes here?
>
> next i
>
> Thanks,
>
> Sandy

Sandy-

Try this for starters:

Sub Fill_Captions()
Dim ctrl As MSForms.Control
Dim i As Integer
i = 1
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
ctrl.Caption = Sheets(1).Cells(i, "A")
i = i + 1
End If
Next
End Sub

---
This assumes the captions you want to use in cells A1:A20 in the first sheet
of your workbook.

HTH
Jason
Atlanta, GA


"Sandy" wrote:

> I have a user form with 20 check boxes. I want to loop through them stuffing
> the captions with various text retrieved from a worksheet.
>
> The checkboxes are consecutively named checkbox1 to checkbox 20.
>
> for i = 1 to 20 step 1
>
> what goes here?
>
> next i
>
> Thanks,
>
> Sandy

Jason, likewise how would I set the value to True or False? I tried changing
..Caption to .Value but that didn't work.

Thank you,

Sandy

"Jason Morin" wrote:

> Sandy-
>
> Try this for starters:
>
> Sub Fill_Captions()
> Dim ctrl As MSForms.Control
> Dim i As Integer
> i = 1
> For Each ctrl In UserForm1.Controls
> If TypeOf ctrl Is MSForms.CheckBox Then
> ctrl.Caption = Sheets(1).Cells(i, "A")
> i = i + 1
> End If
> Next
> End Sub
>
> ---
> This assumes the captions you want to use in cells A1:A20 in the first sheet
> of your workbook.
>
> HTH
> Jason
> Atlanta, GA
>
>
> "Sandy" wrote:
>
> > I have a user form with 20 check boxes. I want to loop through them stuffing
> > the captions with various text retrieved from a worksheet.
> >
> > The checkboxes are consecutively named checkbox1 to checkbox 20.
> >
> > for i = 1 to 20 step 1
> >
> > what goes here?
> >
> > next i
> >
> > Thanks,
> >
> > Sandy

Hi Sandy,

I don't now why that doesn't work for you, it does for me, -
i = 0
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
i = i + 1
ctrl.Caption = Sheets(1).Cells(i, "A")
ctrl.Value = Sheets(1).Cells(i, "B")
End If
Next

If you want to store values between sessions you could 'link' to a cell with
ControlSurce, in Col-C perhaps.

With 20 similar controls probably worth setting up a collection or array of
'WithEvent' class.

Regards,
Peter T


"Sandy" <[email protected]> wrote in message
news:[email protected]...
> Jason, likewise how would I set the value to True or False? I tried
changing
> .Caption to .Value but that didn't work.
>
> Thank you,
>
> Sandy
>
> "Jason Morin" wrote:
>
> > Sandy-
> >
> > Try this for starters:
> >
> > Sub Fill_Captions()
> > Dim ctrl As MSForms.Control
> > Dim i As Integer
> > i = 1
> > For Each ctrl In UserForm1.Controls
> > If TypeOf ctrl Is MSForms.CheckBox Then
> > ctrl.Caption = Sheets(1).Cells(i, "A")
> > i = i + 1
> > End If
> > Next
> > End Sub
> >
> > ---
> > This assumes the captions you want to use in cells A1:A20 in the first
sheet
> > of your workbook.
> >
> > HTH
> > Jason
> > Atlanta, GA
> >
> >
> > "Sandy" wrote:
> >
> > > I have a user form with 20 check boxes. I want to loop through them
stuffing
> > > the captions with various text retrieved from a worksheet.
> > >
> > > The checkboxes are consecutively named checkbox1 to checkbox 20.
> > >
> > > for i = 1 to 20 step 1
> > >
> > > what goes here?
> > >
> > > next i
> > >
> > > Thanks,
> > >
> > > Sandy