excel vba listbox additem

Question

I am running office 365 home on windows 10. I am programming Excel using VBA. I have a data range in a worksheet row. I want the user to be able to select one item from this row of data. I am trying to populate ListBox or ComboBox with dta from the range row. Having read MS vba.reference documentation I decided to get my range data into an array and use listbox = myarray() and got "Object does not support this method or property." I tried looping through my data range and putting each item in using listbox.additem (mydata()) with the same result. On examination of the listbox prperties AddItem is not there. Seems they have been withdrawn or maybe never existed for Excel VBA. Any suggestions?

May be due to you give invalid indexing in listbox...Can you add your code? — MD Ismail Hosen, Sep 28 '20 at 17:30
I have resolved my problem which was due to incorrect sintax. Thank you for responding. — jmcsa3, Oct 01 '20 at 06:53

score 0 · Answer 1 · answered Sep 28 '20 at 17:54

0

If you are using Additem then you should only add a single item not an array. If you want to use an array you have to use List and the array should be one-dimensional

MyListBox.List=MyOneDArray

Personally I never use .List and an array with Listboxes because I found some circumstances in which it did not work as expected.

answered Sep 28 '20 at 17:54

Charles Williams

23,121
5
38
38

Thank you your sample coding helped me to resolve the problem. I had the sintax wrong. I'm still puzzled why some of the combo/list box properties do not appear on the form properties listings but there is probably no answer to that. – jmcsa3 Oct 01 '20 at 06:57
Personally I do prefer assigning an array to `.List`, above all as it is fast and overcomes the built-in limitation to 10 columns of the `.AddItem` method - c.f. [Populate ListBox with multiple columns](https://stackoverflow.com/questions/47528558/vba-excel-populate-listbox-with-multiple-columns/47531440#47531440). - As you stated in your answer *"...I found some circumstances in which it did not work as expected."*, I'd kindly ask you if you could describe some of theses circumstances @CharlesWilliams – T.M. Oct 03 '20 at 15:48

score 0 · Answer 2 · answered Sep 28 '20 at 18:53

There are two ways you can do this:

loop through each item individually and use combobox.additem
Set the combobox.list = array (or variant)

See below for an example of both assuming you want to populate the data from cells A1 to A10 in 2 separate comboboxes:

Private Sub UserForm_Initialize()

Dim i As Integer
Dim arr As Variant
    
' looping through parameters 1-by-1
With UserForm1.ComboBox1
    For i = 1 To 10
        .AddItem ThisWorkbook.Sheets("Sheet1").Range("A" & i).Value
    Next i
End With

' setting combobox list to arrauy
With UserForm1.ComboBox2
    .List = ThisWorkbook.Sheets("Sheet1").Range("A1:A10").Value
End With

End Sub

Make sure to insert your code in the userform code and not the module code

excel vba listbox additem

2 Answers2