View Full Version : Solved: listbox little bug
elsteshepard
04-28-2009, 01:09 AM
Hi I have a userform, which once completed, fills in a worksheet and then gets emailed to 'someone' (user defined)
I think i've got it cracked, but one thing won't work
I have a 2 listboxes, Listbox1 is filled with a range from another sheet, Listbox2 gets filled by highlighting an item from Listbox1 and transferring it with a command button.
when i copy the form using
.Offset(RowCount, 10).Value = Me.ListBox2.List
It only returns the first item
Can anyone help?
JONvdHeyden
04-28-2009, 01:46 AM
Are you trying to return all selected items in ListBox2 to a range? I think you need to loop thru the list box, something like:
For lItem = 0 To Me.ListBox2.ListCount - 1 '// start at zero, not one //
If Me.ListBox2.Selected(lItem) Then
.Range("J" & RowCount) = Me.ListBox2.List(lItem)
RowCount = RowCount + 1
End If
Next lItem
elsteshepard
04-28-2009, 01:50 AM
Not sure
I'm trying to fill a cell in a sheet with the contents of listbox2
so if Listbox2 contains
apples
pears
bananas
The cell on the sheet contains
apples
pears
bananas
JONvdHeyden
04-28-2009, 01:53 AM
Just to be clear, you want one cell to contain all selected items in ListBox2? Are they separated with a carriage return?
georgiboy
04-28-2009, 02:03 AM
Something like this maybe...
Sub ListOneCell()
Dim i As Long
Dim x As Variant
For i = 0 To Me.ListBox1.ListCount - 1
x = x + Me.ListBox1.List(i) & vbNewLine
Next i
Range("A1").Value = x
End Sub
Hope this helps
Bob Phillips
04-28-2009, 02:05 AM
.Offset(Rowcount, 10).Resize(Me.ListBox2.ListCount) = Me.ListBox2.List
georgiboy
04-28-2009, 02:20 AM
This will not add an extra line at the bottom like my previous code.
Sub ListOneCell()
Dim i As Long
Dim x As Variant
For i = 0 To Me.ListBox1.ListCount - 1
If i <> Me.ListBox1.ListCount - 1 Then
x = x + Me.ListBox1.List(i) & vbNewLine
Else
x = x + Me.ListBox1.List(i)
End If
Next i
Range("A1").Value = x
End Sub
elsteshepard
04-28-2009, 02:48 AM
yep, that's correct, one cell to contain all values
The listbox2 has carriage returns (I believe)
elsteshepard
04-28-2009, 02:49 AM
I will try that code thanks
Where would it go?
I have a submit command button, which when pressed, completes the sheet with all the userform data
Can this code go in that?
georgiboy
04-28-2009, 02:52 AM
Yes without the Sub/End Sub lines
Like this...
Private Sub CommandButton1_Click()
Dim i As Long
Dim x As Variant
For i = 0 To Me.ListBox1.ListCount - 1
If i <> Me.ListBox1.ListCount - 1 Then
x = x + Me.ListBox1.List(i) & vbNewLine
Else
x = x + Me.ListBox1.List(i)
End If
Next i
Range("A1").Value = x
End Sub
elsteshepard
04-28-2009, 02:58 AM
xld
tried that one (seemed the simplest!) It works, but it adds each item into a new cell - I need them all in one cell. Can this code be modified?
A normal textbox will return all the values, including carriage returns and multi-line. Obviously a listbox is more tricky?
elsteshepard
04-28-2009, 03:02 AM
thanks georgiboy
one thing though - how does the code know which cell to put the listbox items into?
thanks
georgiboy
04-28-2009, 03:06 AM
This part at the end...
Range("A1").Value = x
...is placing the value of x into range A1
Your range is...
.Offset(RowCount, 10).Value
...so it should look like...
.Offset(RowCount, 10).Value = x
Hope this helps
elsteshepard
04-28-2009, 03:06 AM
hoorah
worked it out myself - aren't I cleverl!
changed range("A1") to equal my offest cell value
and it works!
brilliant
Thanks again
Bob Phillips
04-28-2009, 03:30 AM
xld
tried that one (seemed the simplest!) It works, but it adds each item into a new cell - I need them all in one cell. Can this code be modified?
A normal textbox will return all the values, including carriage returns and multi-line. Obviously a listbox is more tricky?
.Offset(Rowcount, 10).Resize(Me.ListBox2.ListCount).Value = Me.ListBox2.List
.Offset(Rowcount, 10).Value = Join(Application.Transpose(.Offset(Rowcount, 10).Resize(Me.ListBox2.ListCount)), ",")
.Offset(Rowcount + 1, 10).Resize(Me.ListBox2.ListCount - 1).ClearContents
elsteshepard
04-28-2009, 05:09 AM
brilliant, and it doens't have carriage returns either, even better
but...there's always a but
Listbox2 might actually be blank
if it is, i get an error
run-time error '1004'
application defined or object defined erro
i'm guessing i need some kind of if statement?
Bob Phillips
04-28-2009, 07:18 AM
If Me.ListBox2.ListCount > 0 Then
With .Offset(Rowcount, 10)
.Resize(Me.ListBox2.ListCount).Value = Me.ListBox2.List
.Value = Join(Application.Transpose(.Resize(Me.ListBox2.ListCount)), ",")
.Offset(1, 0).Resize(Me.ListBox2.ListCount - 1).ClearContents
End With
End If
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.