how to write vb code for multiple combo boxes

[Guest]

I have several combo boxes with names: drill1, drill2, drill3 etc. I want to
write code that gives a Yes option and a No option in each of those boxes
without having to write code for each one. Is there a way to do this?

thanks

 

[Bob Phillips]

Will these boxes only contain Yes and No? If so, why not just use a
checkbox?

--

HTH

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

 

[Guest]

Yeah, they will only contain yes and no. I don't actually have enough space
in the form to do check boxes.

 

[Jake Marx]

HFB,

If you must create them as ComboBoxes and not CheckBoxes (not sure why
CheckBoxes would be any bigger), here's a way:

Dim ctl As Control

For Each ctl In Controls
If TypeOf ctl Is MSForms.ComboBox Then
If ctl.Name Like "drill*" Then
ctl.Object.AddItem "Yes"
ctl.Object.AddItem "No"
End If
End If
Next ctl

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Bob Phillips]

Because a checkbox only has two states, you can use one checkbox for yes and
no, same number of controls.

--

HTH

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

 

[Guest]

Assuming I copy it into the code exactly the way you've written it there - it
doesn't work...
(I'm obviously not proficient in this...)

HFB,

If you must create them as ComboBoxes and not CheckBoxes (not sure why
CheckBoxes would be any bigger), here's a way:

Dim ctl As Control

For Each ctl In Controls
If TypeOf ctl Is MSForms.ComboBox Then
If ctl.Name Like "drill*" Then
ctl.Object.AddItem "Yes"
ctl.Object.AddItem "No"
End If
End If
Next ctl

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Yeah, they will only contain yes and no. I don't actually have
enough space in the form to do check boxes.

 

[Guest]

Okay. How does the user know what the check means? Or do I go with the
theory that a check means yes and no check means no?

 

[Bob Phillips]

I would have thought a check means yes, seems the logical value to me. It's
what you get all the time in Excel, for instance Tools>Options>General, and
the Gridlines checkbox, I have always assumed that to me yes I want
Gridlines.

 

[Guest]

Okay, I'll go with that thanks.

Now for part two. Same situation, BUT there are about 15 options on the
list. I have 12 combo boxes that each need to display the 15 options. I
know how to do the code for each individual one, but not for all in one hit.
I tried Jake's 'ctl' code thing and it didn't seem to work - I prolly did
something wrong...

 

[Guest]

Make one list: in a worksheet, say range A1:A15
Give you list a Name, Say MyList
In each of your 12 comboboxes type in the MyList in the ListFillRange or
Input range.

 

[Bob Phillips]

Gocush gave you one way, but if you want to do it from code, Jake's answer
can be simplified somewhat, as there is only one combo, so need to loop
through

With ActiveSheet.OLEObjects("ComboBox1").Object
.Clear
.AddItem "Bob"
.AddItem "Lynne"
.AddItem "Amy"
.AddItem "Hannah"
.AddItem "Sita"
.ListIndex = 0
End With

Change the name to suit.

--

HTH

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

 

[Guest]

Jake - assuming I enter the code in as written - this does not work. No
errors or anything, but not values in the combo boxes either. Is some of
what you've written supposed to be replaced with something else according to
my requirements?

thank

HFB,

If you must create them as ComboBoxes and not CheckBoxes (not sure why
CheckBoxes would be any bigger), here's a way:

Dim ctl As Control

For Each ctl In Controls
If TypeOf ctl Is MSForms.ComboBox Then
If ctl.Name Like "drill*" Then
ctl.Object.AddItem "Yes"
ctl.Object.AddItem "No"
End If
End If
Next ctl

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Yeah, they will only contain yes and no. I don't actually have
enough space in the form to do check boxes.

 

[Jake Marx]

I wrote this to work with ComboBoxes on a UserForm. If you have them on a
Worksheet, the syntax will be slightly different:

Dim ctl As OLEObject

For Each ctl In OLEObjects
If TypeOf ctl.Object Is MSForms.ComboBox Then
If ctl.Name Like "drill*" Then
ctl.Object.AddItem "Yes"
ctl.Object.AddItem "No"
End If
End If
Next ctl

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Jake - assuming I enter the code in as written - this does not work.
No errors or anything, but not values in the combo boxes either. Is
some of what you've written supposed to be replaced with something
else according to my requirements?

thank

HFB,

If you must create them as ComboBoxes and not CheckBoxes (not sure
why CheckBoxes would be any bigger), here's a way:

Dim ctl As Control

For Each ctl In Controls
If TypeOf ctl Is MSForms.ComboBox Then
If ctl.Name Like "drill*" Then
ctl.Object.AddItem "Yes"
ctl.Object.AddItem "No"
End If
End If
Next ctl

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Yeah, they will only contain yes and no. I don't actually have
enough space in the form to do check boxes.

:

Will these boxes only contain Yes and No? If so, why not just use a
checkbox?

--

HTH

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


I have several combo boxes with names: drill1, drill2, drill3 etc.
I want to write code that gives a Yes option and a No option in
each of those boxes without having to write code for each one. Is
there a way to do this?

thanks