Excel VBA Exporting 2 Column Listbox to Separate Worksheet

Question

I'm trying to export a 2 column list box to a new worksheet. I want to display each column in the new worksheet. This needs repeating twice as there are 3 listboxes. The user will select the desired row in the listbox and then press a separate 'confirm' command button.

I have written the following code that will export only the first column of each listbox. I have used the RowSource to make the listbox double columned.

Any Help is greatly appreciated.

 Private Sub ConfirmBtn_Click()
 Dim emptyRow As Long

   Sheet2.Activate

   emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

   Cells(emptyRow, 1).Value = SRCLstBox.Value
   Cells(emptyRow, 3).Value = BERLstBox.Value
   Cells(emptyRow, 5).Value = SNKLstBox.Value
End Sub

Answer 1

You can use the List property like this:

With SRCLstBox
    Cells(emptyRow, 1).Value = .Value
    Cells(emptyRow, 2).Value = .List(.ListIndex, 1)
End With
With BERLstBox
    Cells(emptyRow, 3).Value = .Value
    Cells(emptyRow, 4).Value = .List(.ListIndex, 1)
End With
With SNKLstBox
    Cells(emptyRow, 5).Value = .Value
    Cells(emptyRow, 6).Value = .List(.ListIndex, 1)
End With

Answer 2

The Value property of a ListBox will merely give you the content of the first column of the selected item. If no item in the list is selected then you will get NULL as a return value.

To get all items from a ListBox you will need to use the List( , ) method. Here is a small sample Sub to give you the idea:

Public Sub WriteAndReadFromListBox()

Dim lngRow As Long

' Adding 21 rows of 'hello world' in two columns
Load UserForm1
For lngRow = 0 To 20
    UserForm1.ListBox1.AddItem
    UserForm1.ListBox1.List(lngRow, 0) = "hello"
    UserForm1.ListBox1.List(lngRow, 1) = "world"
Next lngRow

' Selecting the first row allows you to use the
' .Value property and get 'hello' (from the first column)
UserForm1.ListBox1.Selected(0) = True
Debug.Print UserForm1.ListBox1.Value

' The following lines of code will write the entire content
' of the ListBox to the sheet with the Index 1
For lngRow = 0 To UserForm1.ListBox1.ListCount
    Sheets(1).Cells(lngRow + 1, 1).Value2 = UserForm1.ListBox1.List(lngRow, 0)
    Sheets(1).Cells(lngRow + 1, 2).Value2 = UserForm1.ListBox1.List(lngRow, 1)
Next lngRow

UserForm1.Show

End Sub