PDA

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

xld
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

xld
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?

xld
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