Listbox to String

[FRIEL]

Hi all

I have a Userform with a listbox called listbox2
I want the entire contents of this list box to be in a string in Variable Recs

I have this

Dim recs As String
If ListBox2.ListCount >= 1 Then
recs = ""
For x = 0 To ListBox2.ListCount - 1
recs = recs & ListBox2.List(x) & " ; "
Next x

End If

And it works
but I believe there is an easier way to do it
I think using the list properity

[snb]

sub snb()
  recs=Join(listbox2.list,";")
End sub

[royUK]

I don't think that you can avoid the loop, how is the listbox populated?

[romperstomper]

If you populated using List or Rowsource (assuming a one column range) then you'll need to transpose before you join; if you used additem, then you need to loop.

[snb]

Sub snb()
  ListBox1.List = Split("aa|bb|cc|dd", "|")
  sn = Join(Application.Transpose(ListBox1.List), "|")
    
  ListBox1.List = Array("aa", "bb", "cc", "dd")
  sn = Join(Application.Transpose(ListBox1.List), "|")
    
  ListBox1.Clear
  ListBox1.AddItem "aa"
  ListBox1.AddItem "bb"
  sn = Join(Application.Transpose(ListBox1.List), "|")

  ListBox1.ListFillRange = "A1:A5"
  sn = Join(Application.Transpose(ListBox1.List), "|")

  ListBox1.ListFillRange = ""
  ListBox1.List = Range("A1:A5").Value
  sn = Join(Application.Transpose(ListBox1.List), "|")

  ListBox1.List = Range("A1:C5").Value
  sn = Join(Application.Transpose(Application.Index(ListBox1.List, , 1)), "|")

  ListBox1.RowSource = "A1:A5"
  sn = Join(Application.Transpose(ListBox1.List), "|")

End Sub

[FRIEL]

  ListBox1.List = ThisWorkbook.Sheets("EMAIL").Range("A2:A36").Value

which method is that?

How do you transpose?

[FRIEL]

[QUOTE=FRIEL;2558478]

  ListBox1.List = ThisWorkbook.Sheets("EMAIL").Range("A2:A36").Value

QUOTE]

sorry this is in the Initialize event on the form

[romperstomper]

Nicely contrived.
Now try this:

Sub snb()
'  ListBox1.List = Split("aa|bb|cc|dd", "|")
'  sn = Join(Application.Transpose(ListBox1.List), "|")
    
'  ListBox1.List = Array("aa", "bb", "cc", "dd")
'  sn = Join(Application.Transpose(ListBox1.List), "|")
    
  ListBox1.Clear
  ListBox1.AddItem "aa"
  ListBox1.AddItem "bb"
  sn = Join(Application.Transpose(ListBox1.List), "|")
End Sub

[FRIEL]

ok having a play with that.

got my head round the examples. i think.
so transpose only works when the listbox is populated in one of the above ways?

so will i be stuck with the loop?

[romperstomper]

Since you haven't told us how you are populating it, we can't really say.

[romperstomper]

Then you need to use Join and application.Transpose as shown.

[FRIEL]

=/

any reason this wont work?

recs = Join(Application.Transpose(ListBox2.List), "|")

[romperstomper]

Depends - how is Listbox2 being populated?

[FRIEL]

=S

ok lil wired
the above works now
it wouldnt work before.

thanks guys

I'd add Rep
but i added Rep to you last time and it wont let me add to you again lol

[FRIEL]

=S Now it dosent!

this is kinda frustrating.....

Populated by

ListBox1.List = ThisWorkbook.Sheets("EMAIL").Range("A2:A36").Value

in the Initialize event

[romperstomper]

You refer to Listbox1 in one code and Listbox2 in the other - which is it? Also, it helps if you tell us what error you are getting: we always want more information than "it doesn't work"

I don't really care about the rep, though I thank you for the thought.

[FRIEL]

Private Sub CommandButton1_Click()
  If ListBox1.ListIndex > -1 Then
    ListBox2.AddItem ListBox1.Value
    ListBox1.RemoveItem ListBox1.ListIndex
    With Cells(1, 30).Resize(ListBox1.ListCount)
      .Value = ListBox1.List
      .Sort Cells(1, 30)
       ListBox1.List = .Value
    End With
  End If
End Sub

like this
what an idiot.
i totaly missed that

Run-time error '13':
Type Mismatch

sorry for the confusion

[romperstomper]

Since you are populating Listbox2 with additem, you'll have to loop.

[FRIEL]

ok
thanks for your help

at least i have learned somthing =D

[snb]

Sub snb()
  ListBox2.Clear
  ListBox2.AddItem "aa"
  ListBox2.AddItem 4
  ListBox2.AddItem #12/9/2011#
  
  Cells(10, 1).Resize(UBound(ListBox2.List) + 1, UBound(ListBox2.List, 2) + 1) = ListBox2.List
  sn = Join(Application.Transpose(Cells(10, 1).Resize(UBound
(ListBox2.List) + 1)), "|")
End Sub

[romperstomper]

I assume you don't expect a response to that?