Excel VBA pass multiple Combobox values to dynamic array

Question

I would like to know if there is a way to add selected multiple combo box values to a dynamic array. So far this my code below, at the moment I can only submit the one selected Combobox value to the array list.

Private Sub UserForm_Initialize()

ComboBox1.AddItem "1"
ComboBox1.AddItem "2"
ComboBox1.AddItem "3"
End Sub

Private Sub CommandButton1_Click()

Dim cmbbox(10) As String
Dim i As Integer

For i = LBound(cmbbox) To UBound(cmbbox)
    cmbbox(i) = ComboBox1.Value
    MsgBox cmbbox(i)
Next i

End Sub

I would like to be able to select a value from the combo box, and then that value gets passed to my array at the 0 position, and then if the another value is selected from the combo box, then that value is passed to my array's 1 position etc...

Tehscript · Accepted Answer · 2017-04-26 14:00:53Z

1

This should do:

For Each Item In ComboBox1.List
    If Not Item Then
        MsgBox Item
    End If
Next

Edit: Did I miss your point here, or did you change your question? According to what I read now, you want to append combobox value at the end of your array each time you hit commandbutton. You should do as follows:

Define your array outside of your sub (at the very top):

Dim cmbbox() As Variant

and the code should look like:

Private Sub CommandButton1_Click()
If Len(Join(cmbbox, "")) = 0 Then 'if your array is empty add the first value from combobox
    ReDim cmbbox(0)
    cmbbox(0) = ComboBox1.Value
Else 'if your array is not empty redim your array and add value from combobox
    ReDim Preserve cmbbox(UBound(cmbbox) + 1)
    cmbbox(UBound(cmbbox)) = ComboBox1.Value
End If

MsgBox "Last Added Item : " & cmbbox(UBound(cmbbox))
End Sub

edited Apr 26, 2017 at 14:00

answered Apr 25, 2017 at 23:24

Tehscript

2,5562 gold badges15 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

A.S.H Over a year ago

If Not Item what is this check supposed to do?

Tehscript Over a year ago

if item is not null

user1810449 Over a year ago

The solution provided by Tehscript is a simpler example of my original loop. But works in the exact same way.

Ambie Over a year ago

What? It does nothing like the same as your code - it's iterating a completely different property, unless I'm missing something obvious.

Tehscript Over a year ago

I think the questions is changed. @Ambie the difference between his code and my first suggestion is: his code loops through same selected combobox value 10 (static) times and my code loops trough all combobox values. This was his request at first but now I see a different question which I responded in edit.

|

Ambie · Accepted Answer · 2017-04-26 02:10:42Z

0

As @Tehscript has indicated, the property you're after is .List which returns a two-dimensional, zero-based array: the first dimension being 'rows' and the second 'columns'.

From your question, it seems as if you want a specific index (or indices) from the row dimension. If your ComboBox only has one column then the second dimension could be hard-coded as zero.

A For Each loop would be okay, but the problem is that it will loop through every item in the array rather than just each row. It might be more efficient, therefore, to run a For [index] loop. Let's say you want the first and third items in your ComboBox, then the code snippet would be:

Dim i As Long
Dim v As Variant

v = ComboBox1.List
For i = 0 To UBound(v, 1)
    If i = 0 Or i = 2 Then
        MsgBox v(i, 0)
    End If
Next

answered Apr 26, 2017 at 2:10

Ambie

4,9872 gold badges16 silver badges30 bronze badges

3 Comments

user1810449 Over a year ago

I would like the user to select the value, once selected then that gets added to the Array. This code doesn't quite to do that.

Ambie Over a year ago

But that's a totally different question from the one you originally asked! I'm not sure that adding this all-important point by way of edit at the end of your question will get it noticed now, so you might want to ask a new question, acknowledging you worded your previous one wrongly. You might also want to spend time making your question absolutely clear. You already wasted @Tehscript's time and mine, so who's to say that you won't change your question again and waste more people's time. Try to be as clear, concise and specific as you can.

user1810449 Over a year ago

The question above has been amended accordingly. And it's not really a waste of time, as its all part of the learning curve. By all means if you can help, please do (but don't rant about it).

Collectives™ on Stack Overflow

Excel VBA pass multiple Combobox values to dynamic array

2 Answers 2

11 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related