How to copy selected listbox item to sheet
 

I have a listbox with 5 columns placed in row 1 on a worksheet.
This listbox have a number of items that the user can select from.
I want to copy the selected index from the listbox to a row on th
worksheet.


I made this small code for a dobbel click on the listbox and with thi
code I can copy a single cell from the selected item in the listbox t
a cell on the worksheet.
And then the next row on the worksheet is selected.


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim rg As Range
Dim rg1 As Range

Set rg = Application.ActiveCell

If rg.Row < 2 Then Exit Sub

Set rg1 = Cells(rg.Row, 1)
rg1 = ListBox1.List(ListBox1.ListIndex)

Set rg1 = Cells(rg.Row + 1, 1)
rg1.Select
ListBox1.Activate

End Sub


But I want to copy all data in the selected item (meaning all columns
and not just the first column.
How can this be done ?

--
michda
-----------------------------------------------------------------------
michdan's Profile: http://www.excelforum.com/member.php...fo&userid=2521
View this thread: http://www.excelforum.com/showthread.php?threadid=38708

How to copy selected listbox item to sheet
 

Where you have this line: rg1 = ListBox1.List(ListBox1.ListIndex)
substitute this:
With rg1
For i = 0 to 4
.Offset(0,i).Cells(1,1).Value = ListBox1.Column(i)
Next i
End With

--
- K Dales


"michdan" wrote:


I have a listbox with 5 columns placed in row 1 on a worksheet.
This listbox have a number of items that the user can select from.
I want to copy the selected index from the listbox to a row on the
worksheet.


I made this small code for a dobbel click on the listbox and with this
code I can copy a single cell from the selected item in the listbox to
a cell on the worksheet.
And then the next row on the worksheet is selected.


Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim rg As Range
Dim rg1 As Range

Set rg = Application.ActiveCell

If rg.Row < 2 Then Exit Sub

Set rg1 = Cells(rg.Row, 1)
rg1 = ListBox1.List(ListBox1.ListIndex)

Set rg1 = Cells(rg.Row + 1, 1)
rg1.Select
ListBox1.Activate

End Sub


But I want to copy all data in the selected item (meaning all columns)
and not just the first column.
How can this be done ??


--
michdan
------------------------------------------------------------------------
michdan's Profile: http://www.excelforum.com/member.php...o&userid=25218
View this thread: http://www.excelforum.com/showthread...hreadid=387080