PDA

View Full Version : [SOLVED] Userform listbox to range



oldman
09-14-2013, 02:23 PM
I am using a userform listbox that is populated using "currentregion" as its source. The listbox is a 6 column (0 to 5?), single select design.

I need the selection from the listbox to populate the first available row of a worksheet but not the data from column 2 and 5(1?).

The first column of data from the listbox is to be in column "C".
Second listbox column of data to column "E".
Third listbox column to column "F"
Fourth listbox colum to column "H".

I have no idea where to start.

oldman

oldman
09-15-2013, 01:20 PM
Many thanks to Kenneth Hobs and his solution. I modified his code to my need.



Private Sub lb1_Click()
Dim lIndex As Integer
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2")

irow = ws.Cells.Find(what:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


With lb1
lIndex = .ListIndex
ws.Cells(irow, 4).Value = .List(lIndex, 1)
ws.Cells(irow, 6).Value = .List(lIndex, 3)
ws.Cells(irow, 7).Value = .List(lIndex, 5)
ws.Cells(irow, 9).Value = .List(lIndex, 6)

End With
End Sub

snb
09-15-2013, 01:52 PM
Or:


Private Sub lb1_Click()
With listbox1
sheets("sheet2").cells(rows.count,3).end(xlup).offset(1).resize(,6)=array(.List(.listindex ,1),"",.List(.listindex,3),.List(.listindex,5),"",.List(.listindex,6))
End With
End Sub

oldman
09-15-2013, 02:08 PM
Thank you. I will apply and test.

Oldman