Need More Help on Stuffing Checkbox control

Jasons suggseted code below worked for the Caption. Likewise how would I set
the .Value for each Checkbox to True or False? I tried changing
..Caption to .Value in his code 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
Was this post helpful to you

Hi,
ctrl.value = "true" works just fine.
for some reason vb does not seem to allow auto fill when you
type ctrl. with value. But if you forse it in, it worked for me.

peter


"Sandy" wrote:

> Jasons suggseted code below worked for the Caption. Likewise how would I set
> the .Value for each Checkbox to True or False? I tried changing
> .Caption to .Value in his code 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
> Was this post helpful to you
>
>

Git it,

Thanks

"peter" wrote:

> Hi,
> ctrl.value = "true" works just fine.
> for some reason vb does not seem to allow auto fill when you
> type ctrl. with value. But if you forse it in, it worked for me.
>
> peter
>
>
> "Sandy" wrote:
>
> > Jasons suggseted code below worked for the Caption. Likewise how would I set
> > the .Value for each Checkbox to True or False? I tried changing
> > .Caption to .Value in his code 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
> > Was this post helpful to you
> >
> >

peter,

Fyi, the "ctrl" variable Intellisense doesn't autofill "value" because it
could be a control without one, like a button. Try intellisense with this
meaningless code and you'll see that "chkbox" autofills "Value" but "ctrl"
doesn't. That's because intellisense knows chkbox is a checkbox and that
they have a "Value" property. With "ctrl" it just takes your word for it
and if your right it compiles.

Dim ctrl As MSForms.Control
Dim chkbox As MSForms.CheckBox

ctrl.Value = False
chkbox.Value = False

For a similar reason, I think, "Activesheet" doesn't have intellisense, but
"ws" dimensioned as a worksheet does. Activesheet could be a chart sheet or
a worksheet, so it doesn't offer you the choices for either, I think.

Not that you asked, but sometimes it helps me to think out loud about these
things.

Doug


"peter" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> ctrl.value = "true" works just fine.
> for some reason vb does not seem to allow auto fill when you
> type ctrl. with value. But if you forse it in, it worked for me.
>
> peter
>
>
> "Sandy" wrote:
>
>> Jasons suggseted code below worked for the Caption. Likewise how would I
>> set
>> the .Value for each Checkbox to True or False? I tried changing
>> .Caption to .Value in his code 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
>> Was this post helpful to you
>>
>>