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

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)


"HFB" wrote in message
...
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

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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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]


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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)


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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...)

"Jake Marx" wrote:

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]


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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 ToolsOptionsGeneral, and
the Gridlines checkbox, I have always assumed that to me yes I want
Gridlines.

--
HTH

Bob Phillips

"HFB" wrote in message
...
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" wrote:

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)


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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...

"Bob Phillips" wrote:

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 ToolsOptionsGeneral, and
the Gridlines checkbox, I have always assumed that to me yes I want
Gridlines.

--
HTH

Bob Phillips

"HFB" wrote in message
...
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" wrote:

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)


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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.

"HFB" wrote:

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...

"Bob Phillips" wrote:

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 ToolsOptionsGeneral, and
the Gridlines checkbox, I have always assumed that to me yes I want
Gridlines.

--
HTH

Bob Phillips

"HFB" wrote in message
...
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" wrote:

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)


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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)


"HFB" wrote in message
...
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...

"Bob Phillips" wrote:

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 ToolsOptionsGeneral,
and
the Gridlines checkbox, I have always assumed that to me yes I want
Gridlines.

--
HTH

Bob Phillips

"HFB" wrote in message
...
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" wrote:

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)


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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

"Jake Marx" wrote:

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]


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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

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]


HFB wrote:
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

"Jake Marx" wrote:

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]


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

"Bob Phillips" wrote:

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)


"HFB" wrote in message
...
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