I want to add an item to an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

Sub test()
Dim cbo As msforms.ComboBox
Dim i As Long

ReDim arr(0 To 5 - 1) As String

For i = 1 To UBound(arr) + 1
arr(i - 1) = CStr(i * 11)
Next

' could reference like this
Set cbo = Worksheets("Sheet1").ComboBox1

' but this gives more flexibility
Set cbo = Worksheets("Sheet1").OLEObjects("ComboBox1").Objec t

'assign entire array (replacing existing)
cbo.List = arr

' add a new 3rd item (1st item is index-0)
cbo.AddItem "abc", 2

' add an item at the end
cbo.AddItem "xyz"

End Sub


Regards,
Peter T


"Daniel Jones" wrote in message
...
I want to add an item to an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson


"Daniel Jones" wrote:

I want to add an item to an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

On Aug 27, 5:18*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson

"Daniel Jones" wrote:
I want to add an item to an activeX combobox that is located on a
worksheet. *Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? *I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

This is awesome - thanks guys! How would I go about deleting all of
the items in the combo box? I figured out how to delete one at a time
but couldn't figure out how to delete all.

Thanks!

Thanks! How would I go about deleting all of the items in the
combobox? I could only discover how to delete one at a time like so

Worksheets("Sheet1").ComboBox1.RemoveItem (0)

On Aug 27, 5:18*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson

"Daniel Jones" wrote:
I want to add an item to an activeX combobox that is located on a
worksheet. *Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:



Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? *I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)


"Daniel Jones" wrote in message
...
Thanks! How would I go about deleting all of the items in the
combobox? I could only discover how to delete one at a time like so

Worksheets("Sheet1").ComboBox1.RemoveItem (0)

On Aug 27, 5:18 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson

"Daniel Jones" wrote:
I want to add an item to an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:



Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

On Aug 28, 12:19*am, "Rick Rothstein"
wrote:
Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)

"Daniel Jones" wrote in message

...
Thanks! How would I go about deleting all of the items in the
combobox? I could only discover how to delete one at a time like so

Worksheets("Sheet1").ComboBox1.RemoveItem (0)

On Aug 27, 5:18 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-

This-.com wrote:
Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson

"Daniel Jones" wrote:
I want to add an item to an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

Thanks! is this supposed to work with an activex combo box (from
control toolbox)? in the help file it says "This method will fail if
it's applied to a built-in command bar control."

Thanks! is this supposed to work with an activex combo
box (from control toolbox)? in the help file it says "This
method will fail if it's applied to a built-in command bar
control."

You could always try any suggestions given to you on a newsgroup... that is
usually faster than posting a question and waiting for an answer. Yes, this
code works on a ComboBox from the Control Toolbox toolbar.

--
Rick (MVP - Excel)


"Daniel Jones" wrote in message
...
On Aug 28, 12:19 am, "Rick Rothstein"
wrote:
Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)

"Daniel Jones" wrote in message

...
Thanks! How would I go about deleting all of the items in the
combobox? I could only discover how to delete one at a time like so

Worksheets("Sheet1").ComboBox1.RemoveItem (0)

On Aug 27, 5:18 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-

This-.com wrote:
Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson

"Daniel Jones" wrote:
I want to add an item to an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add ("Item1")
End Sub

How do I add an item to this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!

On Aug 28, 2:25*pm, "Rick Rothstein"
wrote:
Thanks! is this supposed to work with an activex combo
box (from control toolbox)? in the help file it says "This
method will fail if it's applied to a built-in command bar
control."

You could always try any suggestions given to you on a newsgroup... that is
usually faster than posting a question and waiting for an answer. Yes, this
code works on a ComboBox from the Control Toolbox toolbar.

--
Rick (MVP - Excel)

"Daniel Jones" wrote in message

...
On Aug 28, 12:19 am, "Rick Rothstein"

wrote:
Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)

"Daniel Jones" wrote in message

....
Thanks! How would I go about deleting all of the items in the
combobox? I could only discover how to delete one at a time like so

Worksheets("Sheet1").ComboBox1.RemoveItem (0)

On Aug 27, 5:18 pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-

This-.com wrote:
Sheets("Sheet1").ComboBox1.AddItem ("Item1")
--
HTH...

Jim Thomlinson

"Daniel Jones" wrote:
I want toaddanitemto an activeX combobox that is located on a
worksheet. Note this is not the combbox from the forms toolbar, and
its not the combobox inside of userforms, its the control toolbox
combobox.

I'm getting an object doesn't support this property error when I try
the following:

Sub PopulateList()
Worksheets("Sheet1").ComboBox1.Items.Add("Item1")
End Sub

How do Iaddanitemto this combobox? I don't want to use the
ListFillRange property b/c I will be putting this inside of a loop.

Thanks in advance!


Rick-

You're right. I actually did try it first, I just noticed it when I
was reading the help file. There doesn't seem to be very good help
files on the various methods and properties of control tool box
objects. Do you know of a site that offers a good reference for
this? Thanks for your help.

D

There doesn't seem to be very good help files on
the various methods and properties of control tool
box objects. Do you know of a site that offers a
good reference for this?

You are right, there doesn't seem to be a lot of help for the Control
Toolbox version of the ComboBox. Most properties, methods and events for it
are the same as for the UserForm version of the control and you can get
extensive help for those by putting a ComboBox on the UserForm, selecting it
and hitting the F1 key. Just be aware, these two ComboBoxes do have
differences, so when something from the help files for the UserForm ComboBox
doesn't work on the worksheet ComboBox, you will have to figure out why and
how to code around the difference.

--
Rick (MVP - Excel)

On Aug 28, 3:22*pm, "Rick Rothstein"
wrote:
There doesn't seem to be very good help files on
the various methods and properties of control tool
box objects. *Do you know of a site that offers a
good reference for this?

You are right, there doesn't seem to be a lot of help for the Control
Toolbox version of the ComboBox. Most properties, methods and events for it
are the same as for the UserForm version of the control and you can get
extensive help for those by putting a ComboBox on the UserForm, selecting it
and hitting the F1 key. Just be aware, these two ComboBoxes do have
differences, so when something from the help files for the UserForm ComboBox
doesn't work on the worksheet ComboBox, you will have to figure out why and
how to code around the difference.

--
Rick (MVP - Excel)

Peter - I spent some more time digesting your response, which is
incredibly helpful by the way. Out of curiosity, how exactly does
referencing the control through OLEObjects make it more flexible? Is
that what allows you the ability to assign entire arrays and add items
in certain locations?

Thanks Alot!

My first post in this thread never appeared in my Newsreader, I assumed it
evaporated in the ether. I was a surprised to see your reply!


Set cbo = Worksheets("Sheet1").ComboBox1
or
Set cbo = Worksheets("Sheet1").OLEObjects("ComboBox1").Objec t

These both set a reference to the Combobox, thereafter there's no difference
with what you can do with 'cbo'. Either way works fine.

However the first method must always be hardcoded whereas the second can
accept a string variable, eg

Dim sMyCombo as String
sMyCombo = "ComboBox1"
set cbo = .OLEObjects(sMyCombo).Object

Typically you might have a whole bunch of code that processes different,
albeit similar, controls under given conditions. All you need do is pass the
relevant name as a string. Or maybe you might want to loop controls, etc.

Regards,
Peter T



"Daniel Jones" wrote in message
news:863ba36b-4236-4e3a-9e4d-

Peter - I spent some more time digesting your response, which is
incredibly helpful by the way. Out of curiosity, how exactly does
referencing the control through OLEObjects make it more flexible?
Is that what allows you the ability to assign entire arrays and
add items in certain locations?

Thanks Alot!

I was surprised to see his reply as well... I see no postings by you in this
thread (I'm using Windows Mail as my newsreader).

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
My first post in this thread never appeared in my Newsreader, I assumed it
evaporated in the ether. I was a surprised to see your reply!


Set cbo = Worksheets("Sheet1").ComboBox1
or
Set cbo = Worksheets("Sheet1").OLEObjects("ComboBox1").Objec t

These both set a reference to the Combobox, thereafter there's no
difference with what you can do with 'cbo'. Either way works fine.

However the first method must always be hardcoded whereas the second can
accept a string variable, eg

Dim sMyCombo as String
sMyCombo = "ComboBox1"
set cbo = .OLEObjects(sMyCombo).Object

Typically you might have a whole bunch of code that processes different,
albeit similar, controls under given conditions. All you need do is pass
the relevant name as a string. Or maybe you might want to loop controls,
etc.

Regards,
Peter T



"Daniel Jones" wrote in message
news:863ba36b-4236-4e3a-9e4d-

Peter - I spent some more time digesting your response, which is
incredibly helpful by the way. Out of curiosity, how exactly does
referencing the control through OLEObjects make it more flexible?
Is that what allows you the ability to assign entire arrays and
add items in certain locations?

Thanks Alot!

How odd. I assume your newsreader is hooked into msnews.microsoft.com, like
my OE.
Quite a few of my messages seem to go missing, though this recent stray did
at least make it to Google groups.

http://groups.google.co.uk/group/mic...abf5581813f97#

Daniel - could you advise where you picked up my post and is there any
reason you replied to me by replying on Rick's message

Regards,
Peter T


"Rick Rothstein" wrote in message
...
I was surprised to see his reply as well... I see no postings by you in
this thread (I'm using Windows Mail as my newsreader).

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
My first post in this thread never appeared in my Newsreader, I assumed
it evaporated in the ether. I was a surprised to see your reply!

<snip

Yes, I'm hooked into msnews.microsoft.com.

And I was also wondering why Daniel replied to my message while directing it
to you.

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
How odd. I assume your newsreader is hooked into msnews.microsoft.com,
like my OE.
Quite a few of my messages seem to go missing, though this recent stray
did at least make it to Google groups.

http://groups.google.co.uk/group/mic...abf5581813f97#

Daniel - could you advise where you picked up my post and is there any
reason you replied to me by replying on Rick's message

Regards,
Peter T


"Rick Rothstein" wrote in message
...
I was surprised to see his reply as well... I see no postings by you in
this thread (I'm using Windows Mail as my newsreader).

--
Rick (MVP - Excel)


"Peter T" <peter_t@discussions wrote in message
...
My first post in this thread never appeared in my Newsreader, I assumed
it evaporated in the ether. I was a surprised to see your reply!

<snip

On Aug 30, 1:27*pm, "Rick Rothstein"
wrote:
Yes, I'm hooked into msnews.microsoft.com.

And I was also wondering why Daniel replied to my message while directing it
to you.

--
Rick (MVP - Excel)

"Peter T" <peter_t@discussions wrote in message

...



How odd. I assume your newsreader is hooked into msnews.microsoft.com,
like my OE.
Quite a few of my messages seem to go missing, though this recent stray
did at least make it to Google groups.

http://groups.google.co.uk/group/mic....programming/b...

Daniel - could you advise where you picked up my post and is there any
reason you replied to me by replying on Rick's message

Regards,
Peter T

"Rick Rothstein" wrote in message
...
I was surprised to see his reply as well... I see no postings by you in
this thread (I'm using Windows Mail as my newsreader).

--
Rick (MVP - Excel)

"Peter T" <peter_t@discussions wrote in message
...
My first post in this thread never appeared in my Newsreader, I assumed
it evaporated in the ether. I was a surprised to see your reply!

<snip

Guys- I'm using google groups. I'm new to this forum thing so I
thought I was replying to everyone. In google groups the thread
appears as a conversation, so I assumed everyone would be able to see
my response. Let me know if I should be using it differently.

Thanks,

D

On Aug 29, 4:19*pm, "Peter T" <peter_t@discussions wrote:
My first post in this thread never appeared in my Newsreader, I assumed it
evaporated in the ether. I was a surprised to see your reply!

* * Set cbo = Worksheets("Sheet1").ComboBox1
or
* * Set cbo = Worksheets("Sheet1").OLEObjects("ComboBox1").Objec t

These both set a reference to the Combobox, thereafter there's no difference
with what you can do with 'cbo'. *Either way works fine.

However the first method must always be hardcoded whereas the second can
accept a string variable, eg

Dim sMyCombo as String
* * sMyCombo = "ComboBox1"
* *set cbo = .OLEObjects(sMyCombo).Object

Typically you might have a whole bunch of code that processes different,
albeit similar, controls under given conditions. All you need do is pass the
relevant name as a string. Or maybe you might want to loop controls, etc.

Regards,
Peter T

"Daniel Jones" wrote in message

news:863ba36b-4236-4e3a-9e4d-



Peter - I spent some more time digesting your response, which is
incredibly helpful by the way. *Out of curiosity, how exactly does
referencing the control through OLEObjects make it more flexible?
Is that what allows you the ability to assign entire arrays and
add items in certain locations?
Thanks Alot!

Thanks - This is a big help! I will definitely practice using the
second method - it seems much more flexible.

Although google groups appears as a conversation you can view it in tree
view and reply to individual messages. Indeed you did just that with your
two most recent posts, in reply to Rick and myself.

Previously you had directed a question to me while replying to Rick's
message (also my post was not quoted in yours and so not in context).
Whilst, er, unconventional and a bit confusing it's no problem! I only
asked, and you would not have known, because my first post didn't make it to
MS's server which is where Rick and I, read this ng. That's another subject.

Regards,
Peter T

"Daniel Jones" wrote in message
news:7535efb2-c727-44b4-805d-
Guys- I'm using google groups. I'm new to this forum thing so I
thought I was replying to everyone. In google groups the thread
appears as a conversation, so I assumed everyone would be
able to see my response. Let me know if I should be using
it differently.

Thanks,