Mutliple Selections from Userform paste into one cell

[gsheppar]

I'm trying to run a userform that will allow me to click ok and paste the data into a data entry sheet which is running great. The only exception is that I have 2 listboxes that allow multiple selections, and I'd like to be able to have those selections show up all in one cell in the data.

The 2 listboxes that allow multiple selections are "assignedProject" and "Department". I already have the columns where I want them to be posted in the code below, I'm just not sure how to get the mutliple items to show up in the next available row in that column.

Here is the code:

Private Sub Ok_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Project Entry")

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

'check for a project to be entered
If Trim(Me.projectType.Value) = "" Then
  Me.projectType.SetFocus
  MsgBox "Please enter a project"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.projectType.Value
ws.Cells(iRow, 2).Value = Me.Due.Value
ws.Cells(iRow, 3).Value = Me.Priority.Value
ws.Cells(iRow, 4).Value = Me.assignedProject.Value
ws.Cells(iRow, 5).Value = Me.projectStart.Value
ws.Cells(iRow, 6).Value = Me.projectDue.Value
ws.Cells(iRow, 7).Value = Me.projectCompletion.Value
ws.Cells(iRow, 9).Value = Me.Department.Value
ws.Cells(iRow, 10).Value = Me.projectNotes.Value

'clear the data
Me.projectType.Value = ""
Me.Due.Value = ""
Me.Priority.Value = ""
Me.assignedProject.Value = ""
Me.projectStart.Value = ""
Me.projectDue.Value = ""
Me.projectCompletion.Value = ""
Me.Department.SetFocus
Me.projectNotes.Value = ""

End Sub

 

[Sthrncali]

You have to cycle through each possible answer in the list box like so:

Dim lItem As Long
    For lItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lItem) = True Then
           Sheet2.Range("A65536").End(xlUp)(2, 1) = ListBox1.List(lItem)
           ListBox1.Selected(lItem) = False
        End If
    Next
End Sub

You will need to modify the destination range to your column. Also you will need to loop for each multi-select control you have.

 

[gsheppar]

Would this go in the Listbox Change event, or would this go into the code that I supplied above?

 

[gsheppar]

OK I actually found something that I had done a while ago similar to this, but it turns the data into a string of text which in this case is still ok. But the results I want to tweak and not really sure how to do that.

I get a result like this:
, Item 1, Item 2, Item 3

So I want to have commas and the space in between, but just not at the first. Is this possible?
Here is the code:

Private Sub Ok_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Project Entry")
'New Data
Dim outString As String, i As Long
outString = vbNullString

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

'check for a project to be entered
If Trim(Me.projectType.Value) = "" Then
  Me.projectType.SetFocus
  MsgBox "Please enter a project"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.projectType.Value
ws.Cells(iRow, 2).Value = Me.Due.Value
ws.Cells(iRow, 3).Value = Me.Priority.Value
ws.Cells(iRow, 4).Value = Me.assignedProject.Value
ws.Cells(iRow, 5).Value = Me.projectStart.Value
ws.Cells(iRow, 6).Value = Me.projectDue.Value
ws.Cells(iRow, 7).Value = Me.projectCompletion.Value
'ws.Cells(iRow, 9).Value = Me.Department.Value
With Department
For i = 0 To .ListCount - 1
If .Selected(i) Then outString = outString & ", " & .List(i)
Next i
End With
ws.Cells(iRow, 9).Value = outString

ws.Cells(iRow, 10).Value = Me.projectNotes.Value

'clear the data
Me.projectType.Value = ""
Me.Due.Value = ""
Me.Priority.Value = ""
Me.assignedProject.Value = ""
Me.projectStart.Value = ""
Me.projectDue.Value = ""
Me.projectCompletion.Value = ""
Me.Department.SetFocus
Me.projectNotes.Value = ""

End Sub

 

[Sthrncali]

Just swap ", " & .List(i)

So..

outString = outString & .List(i) & ", "


This will leave a ", " at the end if your string but you can easily trim that part off if needed.

 

[gsheppar]

Awesome. Thanks so much.