VBA Userform ListBox multiple selection

[jordanbuchan359]

Hi,

I'm attempting to build a userform, but have hit a wall..

My userform currently consists of two combo boxes and a multi-select list box. All of which pull their contents from lists within the excel book.

The combo boxes contain the available departments and disciplines, the ListBox has names. To save time, I'd like to be able to select multiple names and assign them to their relevant department / discipline. The only catch, I'd like each person to be listed in a seperate row.

For example, selecting John and Sarah, and assigning them to Stores as Admins:

Name Department Discipline
John Stores Admin
Sarah Stores Admin

Private Sub ContinueButton_Click()

    Me.Hide

    wsEmployee.Select
    
    Range("B2").End(xlDown).Offset(1, 0).Select
    
    ActiveCell.Value = SelectedEmployeeName
    ActiveCell.Offset(0, 1).Value = Combo_Department.Value
    ActiveCell.Offset(0, 2).Value = Combo_Discipline.Value
    
    Unload Me


End Sub

Private Function SelectedEmployeeName() As String

    Dim i As Integer
    
    'For loopcounter = 0 To TechName.ListCount - 1 ' need to include -1 due to zero based index
    
    For i = LBound(TechName.List) To UBound(TechName.List)
    
        If TechName.Selected(i) Then
        
        SelectedTechName = SelectedTechName & TechName.List(i)
            
        End If
    
    Next i
    
    SelectedTechName = Left(SelectedTechName, Len(SelectedTechName) - 1)

End Function

The code above does allow me to select multiple names, but places them in the same cell seperated by a comma. I'm unsure on how to proceed.

Any help would be greatly appreciated.

Thanks,
Jordan

 

[Fluff]

Hi & welcome to MrExcel
How about

Private Sub ContinueButton_Click()
   Dim i As Long, j As Long
   Me.Hide

   wsEmployee.Select
   
   With wsEmployee.Range("B2").End(xlDown).Offset(1, 0)
    
      For i = LBound(TechName.List) To UBound(TechName.List)
         If TechName.Selected(i) Then
            .Offset(j).Value = TechName.List(i)
            .Offset(j, 1).Value = Combo_Department.Value
            .Offset(j, 2).Value = Combo_Discipline.Value
            j = j + 1
         End If
      Next i
   End With
   Unload Me
End Sub

 

[jordanbuchan359]

Hi & welcome to MrExcel
How about

Private Sub ContinueButton_Click()
   Dim i As Long, j As Long
   Me.Hide

   wsEmployee.Select
   
   With wsEmployee.Range("B2").End(xlDown).Offset(1, 0)
    
      For i = LBound(TechName.List) To UBound(TechName.List)
         If TechName.Selected(i) Then
            .Offset(j).Value = TechName.List(i)
            .Offset(j, 1).Value = Combo_Department.Value
            .Offset(j, 2).Value = Combo_Discipline.Value
            j = j + 1
         End If
      Next i
   End With
   Unload Me
End Sub

Thanks Fluff, works a treat!

 

[Fluff]

Glad to help & thanks for the feedback