List box Add Item

[adam]

My listbox1 has 5 columns. I'm trying to add all the 5 columns of the double clicked row to the listbox 2. However im only able to add column 1. How could I overcome this. Any help would be kindly appreciated.

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ListBox2.AddItem ListBox1.List(ListBox1.ListIndex)
End Sub

[HansV]

How about

Code: Select all

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim i As Long
    Dim j As Long
    Dim k As Long
    k = ListBox1.ListIndex
    ' Add item to list box
    ListBox2.AddItem ListBox1.List(k)
    i = ListBox2.ListCount - 1
    ' Add columns
    For j = 1 To 4
        ListBox2.List(i, j) = ListBox1.List(k, j)
    Next j
End Sub

[adam]

It worked fine. Thanks for the help Hans.

[adam]

I'm using the following code to populate my ListBox2 from MS Database.

Code: Select all

Private Sub txtSearch_AfterUpdate()
    Dim cnn         As ADODB.Connection
    Dim rst         As ADODB.Recordset
    Dim strSQL      As String
    Dim ws          As Worksheet
    
    Set ws = Worksheets("Orders")
    
    ws.Range("A2:O10000").ClearContents
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Set cnn = New ADODB.Connection
    cnn.Open "Provider=Microsoft.ace.OLEDB.12.0; " & _
             "Data Source=E:\P\Database.accdb;"
    
    Set rst = New ADODB.Recordset
    
    strSQL = "SELECT [H Code],[T Code],[Department N],[P Name],[L Total] FROM Orders WHERE [Serial No]=" & Me.txtSearch.Value
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdText
    ws.Range("A2").CopyFromRecordset rst
    rst.Close
    
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

    ListBox2.RowSource = "Data"
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True

End Sub

I've set my range as

=OFFSET(Orders!$A$1,1,,COUNTA(Orders!$A$2:$A$10000),5)

However I'm unable to add items from ListBox1 to ListBox2 on double click by the code you've provided.

What may be the reason for this?

I get permission denied error highlighting the following line

Code: Select all

ListBox2.AddItem ListBox1.List(k)

[HansV]

If you have set the RowSource of a listbox to a range, you are not allowed to add or remove individual items using AddItem and RemoveItem any more.
If you want to be able to add items to the list box, you should populate it using AddItem or by setting its List property.

[rory]

Unless you actually need the data in a worksheet for something, I'd suggest you just use:

Code: Select all

    ListBox2.Column = rst.GetRows

instead of:

Code: Select all

    ws.Range("A2").CopyFromRecordset rst
    rst.Close
    
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

    ListBox2.RowSource = "Data"

[adam]

Thank you very much for the help. Really appreciate it.