PC Review


Reply
Thread Tools Rate Thread

Add Item To ActiveX ComboBox

 
 
Daniel Jones
Guest
Posts: n/a
 
      27th Aug 2009
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!
 
Reply With Quote
 
 
 
 
Peter T
Guest
Posts: n/a
 
      27th Aug 2009
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:(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!



 
Reply With Quote
 
 
 
 
Jim Thomlinson
Guest
Posts: n/a
 
      27th Aug 2009
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!
>

 
Reply With Quote
 
Logos
Guest
Posts: n/a
 
      27th Aug 2009
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!

 
Reply With Quote
 
Daniel Jones
Guest
Posts: n/a
 
      27th Aug 2009
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!


 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      28th Aug 2009
Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)


"Daniel Jones" <(E-Mail Removed)> wrote in message
news:(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!


 
Reply With Quote
 
Daniel Jones
Guest
Posts: n/a
 
      28th Aug 2009
On Aug 28, 12:19*am, "Rick Rothstein"
<(E-Mail Removed)> wrote:
> Try this...
>
> Worksheets("Sheet1").ComboBox1.Clear
>
> --
> Rick (MVP - Excel)
>
> "Daniel Jones" <(E-Mail Removed)> wrote in message
>
> news:(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."

 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      28th Aug 2009
> 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:(E-Mail Removed)...
On Aug 28, 12:19 am, "Rick Rothstein"
<(E-Mail Removed)> wrote:
> Try this...
>
> Worksheets("Sheet1").ComboBox1.Clear
>
> --
> Rick (MVP - Excel)
>
> "Daniel Jones" <(E-Mail Removed)> wrote in message
>
> news:(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!


 
Reply With Quote
 
Daniel Jones
Guest
Posts: n/a
 
      28th Aug 2009
On Aug 28, 2:25*pm, "Rick Rothstein"
<(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
>
> news:(E-Mail Removed)...
> On Aug 28, 12:19 am, "Rick Rothstein"
>
> <(E-Mail Removed)> wrote:
> > Try this...

>
> > Worksheets("Sheet1").ComboBox1.Clear

>
> > --
> > Rick (MVP - Excel)

>
> > "Daniel Jones" <(E-Mail Removed)> wrote in message

>
> >news:(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
 
Reply With Quote
 
Rick Rothstein
Guest
Posts: n/a
 
      28th Aug 2009
> 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)

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
dictionary->(item, item, item); Can dictionary point to an array inVBA? cate Microsoft Excel Programming 1 6th Mar 2010 11:15 PM
Re: How to select the first combobox item (ie. Set Combobox.ListIndex = 0) Dirk Goldgar Microsoft Access Form Coding 0 11th Aug 2009 03:10 PM
publish activeX combobox and other activeX control =?Utf-8?B?aXJlbmUgYw==?= Microsoft Excel Programming 0 19th Mar 2007 08:19 AM
Re: How to display the first Item in a combobox as the default item Nigel Microsoft Excel Programming 2 8th Dec 2006 11:21 PM
Re: How to display the first Item in a combobox as the default item Jim Cone Microsoft Excel Programming 0 8th Dec 2006 07:42 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 02:30 PM.