Dump multiple listbox values into one cell

[rvinci]

I've searched and haven't been able to find an answer. I have a lengthy userform that I am adding a listbox into which will allow for multiple selections. On submit, I need all listbox selections to be populated into one cell. The basic coding I have below isn't working. Any help is appreciated.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MASTER - CLIENT ROSTER")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'copy the data to the database
With ws

.Cells(iRow, 1).Value = Me.TextBox1.Value
.Cells(iRow, 2).Value = Me.ComboBox1.Value
.Cells(iRow, 3).Value = Me.ComboBox2.Value
.Cells(iRow, 4).Value = Me.ComboBox3.Value
.Cells(iRow, 5).Value = Me.ComboBox4.Value
.Cells(iRow, 6).Value = Me.ComboBox5.Value
.Cells(iRow, 7).Value = Me.TextBox2.Value
.Cells(iRow, 8).Value = Me.ComboBox6.Value
.Cells(iRow, 10).Value = Me.ListBox1.Value

End With

'clear form
Unload Me

End Sub

 

[Fluff]

Is your listbox just a single column?

 

[rvinci]

Is your listbox just a single column?

Yes, the listbox is a single is a single column with 10 separate values. However many values are selected would all need to populate into one cell for each userform submission....if that makes sense.

 

[Fluff]

Ok, how about

Private Sub CommandButton1_Click()
   Dim iRow As Long
   Dim ws As Worksheet
   Dim i As Long
   Dim Txt As String
   
   With Me.ListBox1
      For i = 0 To .ListCount - 1
         If .Selected(i) = True Then Txt = Txt & ", " & .List(i)
      Next i
   End With
   If Txt <> "" Then Txt = Mid(Txt, 2)
   

   Set ws = Worksheets("MASTER - CLIENT ROSTER")
   
   'find first empty row in database
   iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
   
   'copy the data to the database
   With ws
   
      .Cells(iRow, 1).Value = Me.TextBox1.Value
      .Cells(iRow, 2).Value = Me.ComboBox1.Value
      .Cells(iRow, 3).Value = Me.ComboBox2.Value
      .Cells(iRow, 4).Value = Me.ComboBox3.Value
      .Cells(iRow, 5).Value = Me.ComboBox4.Value
      .Cells(iRow, 6).Value = Me.ComboBox5.Value
      .Cells(iRow, 7).Value = Me.TextBox2.Value
      .Cells(iRow, 8).Value = Me.ComboBox6.Value
      .Cells(iRow, 10).Value = Txt
   
   End With

'clear form
Unload Me
End Sub

 

[rvinci]

Ok, how about

Private Sub CommandButton1_Click()
   Dim iRow As Long
   Dim ws As Worksheet
   Dim i As Long
   Dim Txt As String
  
   With Me.ListBox1
      For i = 0 To .ListCount - 1
         If .Selected(i) = True Then Txt = Txt & ", " & .List(i)
      Next i
   End With
   If Txt <> "" Then Txt = Mid(Txt, 2)
  

   Set ws = Worksheets("MASTER - CLIENT ROSTER")
  
   'find first empty row in database
   iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
  
   'copy the data to the database
   With ws
  
      .Cells(iRow, 1).Value = Me.TextBox1.Value
      .Cells(iRow, 2).Value = Me.ComboBox1.Value
      .Cells(iRow, 3).Value = Me.ComboBox2.Value
      .Cells(iRow, 4).Value = Me.ComboBox3.Value
      .Cells(iRow, 5).Value = Me.ComboBox4.Value
      .Cells(iRow, 6).Value = Me.ComboBox5.Value
      .Cells(iRow, 7).Value = Me.TextBox2.Value
      .Cells(iRow, 8).Value = Me.ComboBox6.Value
      .Cells(iRow, 10).Value = Txt
  
   End With

'clear form
Unload Me
End Sub

That did it, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.