Excel VBA Getting a multiple selection from a listbox

Question

I have a listbox which I set to selectmulti

I am trying to get the values of the selected items with this :

Private Sub CommandButton3_Click()

Dim lItem As Long
Dim nboc As Integer
Dim c As Integer

Range("G:G").Clear
nboc = Worksheets("BDD").Range("IQ2").Value
c = 0
    For lItem = 0 To ListBox2.ListCount - 1
         If ListBox2.Selected(lItem) = True Then
            c = c + 1
            Worksheets("Bordereau Prep").Range("G15:G" & 14 + c) = ListBox2.List(lItem)
            ListBox2.Selected(lItem) = False

        End If
    Next
End Sub

This works as long as I have one item selected. If I have x items selected, it returns x times the first item.

Can you help me? (I am fairly new to VBA and am trying to learn on my own)

mielk · Accepted Answer · 2015-12-15 11:21:18Z

1

With this line:

Worksheets("Bordereau Prep").Range("G15:G" & 14 + c) = ListBox2.List(lItem)

you override the values previously printed by this function.

In first iteration you print the value in each cell of range G15:G15 (a single cell in this case), in second iteration you print the value in each cell of range G15:G16 (so you override the value printed in first iteration) and so on.

You need to change this line like below:

Worksheets("Bordereau Prep").Range("G14").Offset(c, 0) = ListBox2.List(lItem)

answered Dec 15, 2015 at 11:21

mielk

3,94014 silver badges20 bronze badges

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

1 Comment

Amandine FAURILLOU Over a year ago

That's exactly what I was looking for

Vityata · Accepted Answer · 2016-01-18 16:42:00Z

Your problem is in the line:

Worksheets("Bordereau Prep").Range("G15:G" & 14 + c) = ListBox2.List(lItem) It simply assigns the whole range to the last found value. Here is something that works for you, you may use it somehow further and change it.

Option Explicit

Sub btn()

Dim lItem   As Long
Dim c       As Long

Range("G:G").Clear

For lItem = 0 To Worksheets("Bordereau Prep").ListBox2.ListCount - 1
    If Worksheets("Bordereau Prep").ListBox2.Selected(lItem) = True Then
        c = c + 1
        Worksheets("BDD").Cells(15 + c, 7) = Worksheets("Bordereau Prep").ListBox2.List(lItem)
        'Worksheets("Bordereau Prep").Range("G15:G" & 14 + c) = Worksheets("Bordereau Prep").ListBox2.List(lItem)
        Worksheets("Bordereau Prep").ListBox2.Selected(lItem) = False
    End If
Next lItem

End Sub

Last but not least, you try not to use Integers in VBA, but longs. Enjoy it!

Collectives™ on Stack Overflow

Excel VBA Getting a multiple selection from a listbox

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related