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?

With Userform1
for i = 0 to 2
cells(rw,i+1) = .Listbox1.list(.Listbox1.listindex,i)
Next
End With


--
Regards,
Tom Ogilvy



"GeorgeFD29" <[email protected]> wrote in message
news:[email protected]...
> 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?

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > >
> > >
> > >
>
>
>