Fah
Fah

Reputation: 203

Multi-selection values from ListBox to paste in different cells in another sheet

I have a listbox with the option of multi-selection and I need to paste the values selected (maximum 5 values) to 5 different cell in sheet named grid_2 (each value need to be in a different cell the cell are Grid_2.range("A1:E1"). I manage to paste the 1st value on the cell, however the others repeat the last selection.

the result I am getting

I am trying to fix my code before paste the 5 values selected in the cells, but for now I am able only to have 1 unique value (the first selection from listbox in the cell "A1") and the value on the cells "B1", "C1" etc will bill the same as the last selection. However, I need each selection to be populated in 1 cell and not have duplicate values or repeat the same value.

  Public Sub Select_Bene_Click()
    
     Dim addme As Range, addme1 As Range, addme2 As Range
    Dim x As Integer, y As Integer, Ck As Integer
    
        Set addme = grid_2.Range("A1")
        Set addme1 = grid_2.Range("B1")
        Set addme2 = grid_2.Range("C1")
        Ck = 0
    
             For x = 0 To Me.List_Bene.ListCount - 1
             If Me.List_Bene.Selected(x) Then
             Ck = 1
    
             If addme = "" Then
             addme.Value = Me.List_Bene.List(x, 0)
    
             Else
            addme1.Value = Me.List_Bene.List(x, 0)
    
             If addme1 = "" Then
             addme1.Value = Me.List_Bene.List(x, 0)
    
             ElseIf addme1 <> "" Then
             addme2.Value = Me.List_Bene.List(x, 0)
    
                End If
              End If
       End If
    Next x
  Unload Me
End Sub

Upvotes: 1

Views: 1089

Answers (2)

TinMan
TinMan

Reputation: 7759

It simpler to increment a counter and use grid_2.Range("A1").Cells(1, counter) to target your range.

 Public Sub Select_Bene_Click()
    
    Dim Target As Range
    Dim x As Long, y As Long, c As Long
    
    Set Target = grid_2.Range("A1")

    For x = 0 To Me.List_Bene.ListCount - 1
    
        If Me.List_Bene.Selected(x) Then
            c = c + 1
            Target.Cells(1, c).Value = Me.List_Bene.List(x, 0)
            
            If c = 5 Then Exit For
        End If
        
    Next x
    
    Me.Hide
End Sub

Upvotes: 1

Zac
Zac

Reputation: 1942

Try replacing the code you have in Public Sub Select_Bene_Click() to the code below. Hopefully that should do the trick

Public Sub Select_Bene_Click()

    Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet3")   '<- Change sheet name
    Dim iItem As Long, iC As Long: iC = 0
    Dim aRng As Variant: aRng = Array("A1", "B1", "C1", "D1", "E1")     '<- Make sure range is what you want
    
    For iItem = 0 To Me.lstTesting.ListCount - 1                        '<- Change the name of listbox
    
        If Me.lstTesting.Selected(iItem) Then                           '<- Change the name of listbox
            oWS.Range(aRng(iC)).Value = Me.lstTesting.List(iItem)       '<- Change the name of listbox
            iC = iC + 1
            
            If iC > UBound(aRng) Then Exit For
        End If
        
    Next
    
End Sub

Upvotes: 2

Related Questions