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

'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" <(E-Mail Removed)> wrote in message
news:b41603a5-4517-42ce-86b1-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
news:bc8cfc4b-44af-484c-b57d-(E-Mail Removed)...
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"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Try this...
>
> Worksheets("Sheet1").ComboBox1.Clear
>
> --
> Rick (MVP - Excel)
>
> "Daniel Jones" <95bu...@gmail.com> wrote in message
>
> news:bc8cfc4b-44af-484c-b57d-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
news:8a2bdf08-04d0-43a2-9336-(E-Mail Removed)...
On Aug 28, 12:19 am, "Rick Rothstein"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> Try this...
>
> Worksheets("Sheet1").ComboBox1.Clear
>
> --
> Rick (MVP - Excel)
>
> "Daniel Jones" <95bu...@gmail.com> wrote in message
>
> news:bc8cfc4b-44af-484c-b57d-(E-Mail Removed)...
> 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"
<rick.newsNO.S...@NO.SPAMverizon.net> 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" <95bu...@gmail.com> wrote in message
>
> news:8a2bdf08-04d0-43a2-9336-(E-Mail Removed)...
> On Aug 28, 12:19 am, "Rick Rothstein"
>
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > Try this...
>
> > Worksheets("Sheet1").ComboBox1.Clear
>
> > --
> > Rick (MVP - Excel)
>
> > "Daniel Jones" <95bu...@gmail.com> wrote in message
>
> >news:bc8cfc4b-44af-484c-b57d-(E-Mail Removed)....
> > 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)