Thread: How do I select and display multiple entries?

Hello sunny17,

I have added a UserForm that displays the names of the testers on "Sheet1". Each selection in the ListBox has a checkbox next to it. Once the choices are made, the user clicks the "Enter" button. The names are then transferred to the active cell in column "F" as a comma separated string.

There are several parts to the workbook: A UserForm, a module, and the Worksheet Selection Change event. The UserForm lets the user select the testers, the module contains the macro to display the UserForm, and the worksheet event validates the user is in column "F" before displaying the UserForm.. Here is the all the code that has been added to the attached workbook.

UserForm Code

Private Sub CommandButton1_Click()

  Dim I As Integer
  Dim Text As String
  
    For I = 0 To ListBox1.ListCount - 1
      If ListBox1.Selected(I) = True Then
         Text = Text & ListBox1.List(I) & ","
         ListBox1.Selected(I) = False
      End If
    Next I
    
    If Text <> "" Then Text = Left(Text, Len(Text) - 1)
    ActiveCell = Text
    
    Me.Hide
    
End Sub

Private Sub UserForm_Initialize()

  Dim Rng As Range
  Dim RngEnd As Range
  
    With Worksheets("Sheet1")
      Set Rng = .Range("AB2")
      Set RngEnd = .Cells(Rows.Count, Rng.Column).End(xlUp)
      Set Rng = IIf(RngEnd.Row < Rng.Row, Rng, .Range(Rng, RngEnd))
    End With
    
    ListBox1.List = Rng.Value
    
End Sub

Sheet1 Worksheet Selection Change Event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  
  If Target.Count > 1 Then Exit Sub
  If Target.Column = 6 And Target.Row > 1 Then GetTesters
    
End Sub

Module1 Code

Sub GetTesters()
  UserForm1.Show
End Sub