Transferring 2 columns of data from one listbox to another

[VBAhelp33]

Hi all,

I am wanting to pull 2 columns of data from an excel spreadsheet into a list box & then would like to move 2 columns into the second list box but I can’t seem to get the second column to show through.

I got this code of the internet. but it doesn’t pull the second column of data into the second list box. How can I make the code in CommandButton2 pick up the 2nd column?

I am teaching myself VBA so apologies if this is a very basic question.

Private Sub CommandButton2_Click()

Dim iCtr As Long
For iCtr = 0 To Me.ListBox2.ListCount - 1

If Me.ListBox2.Selected(iCtr) = True Then

Me.lstSelector.AddItem Me.ListBox2.List(iCtr)

End If

Next iCtr
For iCtr = Me.ListBox2.ListCount - 1 To 0 Step -1

If Me.ListBox2.Selected(iCtr) = True Then

Me.ListBox2.RemoveItem iCtr

End If
Next iCtr

End Sub



Private Sub UserForm_Initialize()

Dim i As Long
For i = 2 To Sheet1.Range("A100000").End(xlUp).Row

Me.lstSelector.AddItem Sheet1.Cells(i, 1)

Me.lstSelector.List(lstSelector.ListCount - 1, 1) = Sheet1.Cells(i, 2)

Next i

End Sub

 

[DanteAmor]

Hi and welcome to MrExcel.

I did not understand very well, do you want to pass data from Listbox2 to the lstSelector?

Add this line.

Private Sub CommandButton2_Click()
  Dim iCtr As Long
  With ListBox2
    For iCtr = 0 To .ListCount - 1
      If .Selected(iCtr) = True Then
        lstSelector.AddItem .List(iCtr)
        lstSelector.List(lstSelector.ListCount - 1, 1) = .List(iCtr, 1)
      End If
    Next iCtr
    For iCtr = .ListCount - 1 To 0 Step -1
      If .Selected(iCtr) = True Then
        .RemoveItem iCtr
      End If
    Next iCtr
  End With
End Sub

 

[VBAhelp33]

Hi there,

Many thanks for your response.

So I am trying to move 2 columns of data from listbox1 (lstSelector) to listbox2. But when I click on CommandButton2 only the first column in listbox1 moves to listbox2.

 

[DanteAmor]

trying to move 2 columns of data from listbox1 (lstSelector) to listbox2.

Try this:

Private Sub CommandButton2_Click()
  Dim iCtr As Long
  With lstSelector
    For iCtr = 0 To .ListCount - 1
      If .Selected(iCtr) = True Then
        ListBox2.AddItem .List(iCtr)
        ListBox2.List(ListBox2.ListCount - 1, 1) = .List(iCtr, 1)
      End If
    Next iCtr
    For iCtr = .ListCount - 1 To 0 Step -1
      If .Selected(iCtr) = True Then
        .RemoveItem iCtr
      End If
    Next iCtr
  End With
End Sub

 

[VBAhelp33]

Thank you so much! That worked perfectly.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.