PDA

View Full Version : Single Cell to ListBox



spaceships
09-14-2012, 09:00 AM
Hello,

I managed to write the code required to read from the ListBox to a single cell and separate each entry in the listbox by an Alt+Enter value (vbLf). The code for this is:

Private Function LogListToCell() As String
Dim x As Integer
Dim list As String
x = 0
For x = 0 To Log_List.ListCount - 1
If x <> Log_List.ListCount - 1 Then _
list = list & Log_List.Column(0, x) & vbLf _
Else _
list = list & Log_List.Column(0, x)
Next
LogListToCell = list
End Function


So what I'm trying to do now is to read those values back into the ListBox and I'm not quite sure how to go about this.

Teeroy
09-14-2012, 05:24 PM
Hi Spaceships

Welcome to the forum. A simpler way for you to do what you want is to use the JOIN function:
(note I've added userform1 to the control name to give it a full reference to where I was testing the control)

Private Function LogListToCell2() As String
Dim list
list = Application.Transpose(UserForm1.Log_List.list)
LogListToCell2 = Join(list, vbLf)
End Function
and to replace the list values I'd use a sub rather than function since you're not returning anything and the SPLIT function parses the cell contents nicely

Private Sub CellToLogList(location As Range)
Dim list
list = Split(location.Value, vbLf)
UserForm1.Log_List.list = list
End Sub

snb
09-15-2012, 06:18 AM
or
Sub snb()
ComboBox1.List = Array(1, 2, 3, 4, 5, 67)


c01 = Join(Application.Transpose(ComboBox1.List), vbcr)
ComboBox1.List = Split(c01, vbcr)
End Sub