CODEWITHSUNDEEP
vbaexcellistbox
Amandine FAURILLOU
Amandine FAURILLOU

Reputation: 612

Excel VBA Getting a multiple selection from a listbox

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)

Upvotes: 1

Views: 5737

Answers (2)

Vityata
Vityata

Reputation: 43575

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!

Upvotes: 1

mielk
mielk

Reputation: 3940

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)

Upvotes: 1

Related Questions