Need to loop through all items in a combobox...

[hollifd]

How do I loop through each item in a combobox using VBA and display each item using msgbox. Ultimately, I want to check to see if an item is already in a combobox before I add the item using combobox.additem

I hope it makes sense.

Thanks for any help,

David

 

[Norie]

David

What type of combobox is it?

One from the Control Toolbox toolbar, or the Forms toolbar, or on a userform?

What are you actually trying to populate it with?

 

[BatmanUK]

David,

Assuming the ComboBox is on a UserForm, try the following

Private Sub UserForm_Initialize()
    Me.ComboBox1.AddItem "a"
    Me.ComboBox1.AddItem "b"
    Me.ComboBox1.AddItem "c"
    Me.ComboBox1.AddItem "d"
End Sub

Private Sub CommandButton1_Click()
    Dim intComboItem As Integer
    
    For intComboItem = 0 To Me.ComboBox1.ListCount - 1
        MsgBox Me.ComboBox1.List(intComboItem)
    Next
End Sub

Hope this helps.

 

[hollifd]

Thanks for your time Norie,

It is a combobox on a userform. I will read a text file and add the items from the text file to the combobox. The text file has items that are repeated and I do not want to repeat the items in the combobox. I have not been able to figure out how to read the items inside the combobox so that I can check to see if the item already exists before adding it. I can do this outside of Excel if you do not have a easy alternative.

David

 

[hollifd]

BatmanUK,

That is what I needed. Thanks to you and Norie for your help.

David

 

[Norie]

David

You'll find code on the forum that does this.

2 methods I can think of

1 Import said text file to a (new?)(hidden?) worksheet and use advanced filter to get the unique values.

2 Use a Collection.

Try search to find out more info/code.

Oh, just thought of another method(s) - ADO/DAO to run a query on the text file that only returns unique values.

 

[JohnnyFun]

I had to use "ItemData" myself:

ComboBox1.ItemData(index)