Excel VBA-Looping through Multiselection

[jpendegraft]

Essentially, I am trying to return the value in a text box onto a
worksheet for all items that are selected.

The column is based off of a combo box drop down.
And the row is based off of the list box.

I am struggling to loop though a multiselection in a list box from a
userform. I can only get the last item on the selection to
populate......

My code is as follows:

For i = 1 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then _
r = ListBox1.ListIndex + 2
Select Case True
Case ComboBox1.ListIndex = 0: Cells(r, 6) = TextBox1.Value
Case ComboBox1.ListIndex = 1: Cells(r, 7) = TextBox1.Value
Case ComboBox1.ListIndex = 2: Cells(r, 8) = TextBox1.Value
Case ComboBox1.ListIndex = 3: Cells(r, 9) = TextBox1.Value
Case ComboBox1.ListIndex = 4: Cells(r, 10) =
TextBox1.Value
End Select
Next i


Any help would be greatly appreciated!

 

[Tom Ogilvy]

First, the first entry is zero not 1

Second. ListIndex won't change in your loop - you need to use "i" to get the
row. I made it I plus 2, but you may need to adjust the 2.

For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) = True Then _
r = i + 2
Select Case True
Case ComboBox1.ListIndex = 0: Cells(r, 6) = TextBox1.Value
Case ComboBox1.ListIndex = 1: Cells(r, 7) = TextBox1.Value
Case ComboBox1.ListIndex = 2: Cells(r, 8) = TextBox1.Value
Case ComboBox1.ListIndex = 3: Cells(r, 9) = TextBox1.Value
Case ComboBox1.ListIndex = 4: Cells(r, 10) = TextBox1.Value
End Select
Next i