Add Item To ActiveX ComboBox

[Daniel Jones]

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!

 

[Peter T]

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

 

[Jim Thomlinson]

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

 

[Logos]

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

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!

 

[Daniel Jones]

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)

 

[Rick Rothstein]

Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)


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)

 

[Daniel Jones]

Try this...

Worksheets("Sheet1").ComboBox1.Clear

--
Rick (MVP - Excel)


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)

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

 

[Rick Rothstein]

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.

 

[Daniel Jones]

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-

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

 

[Rick Rothstein]

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.

 

[Daniel Jones]

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

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!

 

[Peter T]

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

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

 

[Rick Rothstein]

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

 

[Peter T]

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/mi...ogramming/browse_frm/thread/d91abf5581813f97#

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

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

<snip>

 

[Rick Rothstein]

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

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

 

[Daniel Jones]

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

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

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

 

[Daniel Jones]

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

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

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

 

[Peter T]

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