Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning values multi column listbox
I have a three column listbox. What I'd like to do, is return the data in
all three columns to a cells in a worksheet. However, all I'm able to do is return the first column's data. Anyone have any thoughts? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning values multi column listbox
With Userform1
for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With -- Regards, Tom Ogilvy "GeorgeFD29" wrote in message ... I have a three column listbox. What I'd like to do, is return the data in all three columns to a cells in a worksheet. However, all I'm able to do is return the first column's data. Anyone have any thoughts? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning values multi column listbox
Tom,
When I apply the code: With Userform1 for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With I get an 'application defined or object defined error'. I put the code on the doubleclick event of the list box. This is the code that sets up the 3 column list box: ReDim b(c, 3) For x = 0 To c - 1 b(x, 0) = a(x, 0) b(x, 1) = a(x, 1) b(x, 2) = a(x, 2) Next ListBox1.List = b and what I tried to do was to return the data in column A of the box to ie row1 columnA of the spreadsheet, data in column B of the listbox to row1 columnB and data from the third column of the listbox to row1 columnC Thanks for helping, George "Tom Ogilvy" wrote: With Userform1 for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With -- Regards, Tom Ogilvy "GeorgeFD29" wrote in message ... I have a three column listbox. What I'd like to do, is return the data in all three columns to a cells in a worksheet. However, all I'm able to do is return the first column's data. Anyone have any thoughts? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning values multi column listbox
This worked fine for me:
Private Sub CommandButton1_Click() Unload Me End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Set sh = Worksheets("sheet1") rw = 5 With UserForm1 For i = 0 To 2 sh.Cells(rw, i + 1).Value = _ .ListBox1.List(.ListBox1.ListIndex, i) Next End With End Sub Private Sub UserForm_Initialize() ListBox1.ColumnCount = 3 Dim a(), b() c = 20 ReDim a(0 To c - 1, 0 To 2) For i = 0 To c - 1 For j = 0 To 2 a(i, j) = Int(Rnd() * 100 + 1) Next Next ReDim b(0 To c - 1, 0 To 2) For x = 0 To c - 1 b(x, 0) = a(x, 0) b(x, 1) = a(x, 1) b(x, 2) = a(x, 2) Next ListBox1.List = b End Sub -- Regards, Tom Ogilvy "GeorgeFD29" wrote in message ... Tom, When I apply the code: With Userform1 for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With I get an 'application defined or object defined error'. I put the code on the doubleclick event of the list box. This is the code that sets up the 3 column list box: ReDim b(c, 3) For x = 0 To c - 1 b(x, 0) = a(x, 0) b(x, 1) = a(x, 1) b(x, 2) = a(x, 2) Next ListBox1.List = b and what I tried to do was to return the data in column A of the box to ie row1 columnA of the spreadsheet, data in column B of the listbox to row1 columnB and data from the third column of the listbox to row1 columnC Thanks for helping, George "Tom Ogilvy" wrote: With Userform1 for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With -- Regards, Tom Ogilvy "GeorgeFD29" wrote in message ... I have a three column listbox. What I'd like to do, is return the data in all three columns to a cells in a worksheet. However, all I'm able to do is return the first column's data. Anyone have any thoughts? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
returning values multi column listbox
Works great! Thanks Tom.
"Tom Ogilvy" wrote: This worked fine for me: Private Sub CommandButton1_Click() Unload Me End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) Set sh = Worksheets("sheet1") rw = 5 With UserForm1 For i = 0 To 2 sh.Cells(rw, i + 1).Value = _ .ListBox1.List(.ListBox1.ListIndex, i) Next End With End Sub Private Sub UserForm_Initialize() ListBox1.ColumnCount = 3 Dim a(), b() c = 20 ReDim a(0 To c - 1, 0 To 2) For i = 0 To c - 1 For j = 0 To 2 a(i, j) = Int(Rnd() * 100 + 1) Next Next ReDim b(0 To c - 1, 0 To 2) For x = 0 To c - 1 b(x, 0) = a(x, 0) b(x, 1) = a(x, 1) b(x, 2) = a(x, 2) Next ListBox1.List = b End Sub -- Regards, Tom Ogilvy "GeorgeFD29" wrote in message ... Tom, When I apply the code: With Userform1 for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With I get an 'application defined or object defined error'. I put the code on the doubleclick event of the list box. This is the code that sets up the 3 column list box: ReDim b(c, 3) For x = 0 To c - 1 b(x, 0) = a(x, 0) b(x, 1) = a(x, 1) b(x, 2) = a(x, 2) Next ListBox1.List = b and what I tried to do was to return the data in column A of the box to ie row1 columnA of the spreadsheet, data in column B of the listbox to row1 columnB and data from the third column of the listbox to row1 columnC Thanks for helping, George "Tom Ogilvy" wrote: With Userform1 for i = 0 to 2 cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i) Next End With -- Regards, Tom Ogilvy "GeorgeFD29" wrote in message ... I have a three column listbox. What I'd like to do, is return the data in all three columns to a cells in a worksheet. However, all I'm able to do is return the first column's data. Anyone have any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Multi Column ListBox | Excel Programming | |||
Multi-Column Listbox properties | Excel Programming | |||
populating a multi-column Listbox | Excel Programming | |||
populating a multi-column Listbox | Excel Programming | |||
Extract values from a multi-select multi-column list-box | Excel Programming |