Solved: listbox little bug

[elsteshepard]

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]

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:

[vba]
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
[/vba]

[elsteshepard]

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]

Just to be clear, you want one cell to contain all selected items in ListBox2? Are they separated with a carriage return?

[georgiboy]

Something like this maybe...

[VBA]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[/VBA]

Hope this helps

[Bob Phillips]

[vba]

.Offset(Rowcount, 10).Resize(Me.ListBox2.ListCount) = Me.ListBox2.List
[/vba]

[georgiboy]

This will not add an extra line at the bottom like my previous code.

[vba]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[/vba]

[elsteshepard]

yep, that's correct, one cell to contain all values

The listbox2 has carriage returns (I believe)

[elsteshepard]

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]

Yes without the Sub/End Sub lines

Like this...

[VBA]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[/VBA]

[elsteshepard]

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]

thanks georgiboy

one thing though - how does the code know which cell to put the listbox items into?

thanks

[elsteshepard]

hoorah
worked it out myself - aren't I cleverl!

changed range("A1") to equal my offest cell value

and it works!

brilliant

Thanks again

[georgiboy]

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

[Bob Phillips]

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?

[vba]

.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
[/vba]

[elsteshepard]

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]

[vba]

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
[/vba]