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?

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?

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?

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?

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?