Selecting a team from a ListBox

[HTSCF Fareha]

I have a simple form that allows for the selection of team members to be assigned to a work task.

My form has a ComboBox which allows for selection of either Team 1, Team 2 or Team 3.

Depending on the selection, I then have a ListBox with members associated with the team selected. The form user will then select multiple names from this ListBox, to be transferred to a TextBox prior to the ‘Enter’ button being pressed to fill in the names at the correct place in the form via a Bookmark. The reason for the need for the TextBox to be populated prior to the enter button is to allow for the form user to manually add names that are not members of any of the three teams.

My form at the moment only works as far as allowing for a selection from the ComboBox. Not a very good start really!

Help would be very much appreciated, thanks.

Option Explicit

Private Sub EnterBut_Click()
    Dim oTeamMembers As Range
    
    'Check if a team has been selected
    If ComboBox1.ListIndex = 0 Then
        MsgBox "Select Team", vbCritical, "Triage Hub"
        ComboBox1.SetFocus
        Exit Sub
    End If
    
    'Check if team members have been selected
    If TextBox1.Text = "" Then
        MsgBox "Select Team Members", vbCritical, "Triage Hub"
        TextBox1.SetFocus
        Exit Sub
    End If
    
    'use FillBM function to write bookmarks
    FillBM "TeamMembers", TextBox1.Text
    
    Set oTeamMembers = Nothing
    Unload Me
    lbl_Exit
    Exit Sub
    
End Sub

Private Sub UserForm_Initialize()
    Dim myArray()   As String
    
    'Create list of teams
    myArray = Split("Select Team|Team 1|Team 2|Team 3", "|")
    
    'Use List method to populate ComboBox
    ComboBox1.List = myArray
    ComboBox1.ListIndex = 0
    
    'Redefine list for team members Team 1
    myArray = Split("Select Team Members Team 1|Dave|Rob|Sarah|Dave|Rob|Sarah|Liz|Mike", "|")

    'Redefine list for team members Team 2
    myArray = Split("Select Team Members Team 2|Mike|June|Mary|John|Steve|Maria|Liz|Andy", "|")

    'Redefine list for team members Team 3
    myArray = Split("Select Team Members Team 3|Steve|John|Mary|Ivan|Dan|Lisa|Ian|Joan", "|")
    
    lbl_Exit:
    Exit Sub
End Sub

Private Sub ListBox1_Change()
    If ListBox1.ListIndex > 0 Then
        TextBox1.Text = ListBox1.Value
    End If
    
End Sub

Private Sub FillBM(strbmName As String, strValue As String)
    'Graham Mayor - http://www.gmayor.com
    Dim oRng        As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        If .Bookmarks.Exists(strbmName) = TRUE Then
            Set oRng = .Bookmarks(strbmName).Range
            oRng.Text = strValue
            oRng.Bookmarks.Add strbmName
        End If
    End With
    lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

[gmaxey]

Not very elegant, but should work:

Option Explicit
Dim myArray()   As String

Private Sub ComboBox1_Change()
  Select Case ComboBox1.ListIndex
    Case 0
      ListBox1.Clear
      
    Case 1
        'Redefine list for team members Team 1
    myArray = Split("Select Team Members Team 1|Dave|Rob|Sarah|Dave|Rob|Sarah|Liz|Mike", "|")
ListBox1.List = myArray
    Case 2
        'Redefine list for team members Team 2
    myArray = Split("Select Team Members Team 2|Mike|June|Mary|John|Steve|Maria|Liz|Andy", "|")
ListBox1.List = myArray


    Case 3
        'Redefine list for team members Team 3
    myArray = Split("Select Team Members Team 3|Steve|John|Mary|Ivan|Dan|Lisa|Ian|Joan", "|")
ListBox1.List = myArray

  End Select
  

End Sub

Private Sub UserForm_Initialize()

  'Create list of teams
  myArray = Split("Select Team|Team 1|Team 2|Team 3", "|")
  'Use List method to populate ComboBox
  ComboBox1.List = myArray
  ComboBox1.ListIndex = 0
  ListBox1.MultiSelect = fmMultiSelectMulti
    
lbl_Exit:
    Exit Sub
End Sub

Private Sub ListBox1_Change()
Dim lngIndex As Long, lngCOunt As Long
Dim arrTMs() As String
  lngCOunt = 0
  For lngIndex = 1 To ListBox1.ListCount - 1
    If ListBox1.Selected(lngIndex) Then
      ReDim Preserve arrTMs(lngCOunt)
      lngCOunt = lngCOunt + 1
      arrTMs(UBound(arrTMs)) = ListBox1.List(lngIndex)
    End If
  Next lngIndex
  If IsArray(arrTMs) Then TextBox1 = fcnArrayToCommaAndDelimtedList(arrTMs)
End Sub

'**** Create a Comma/And delimited list.
Public Function fcnArrayToCommaAndDelimtedList(varIn As Variant, Optional bOxford As Boolean = False) As String
Dim strTemp As String
Dim lngIndex As Long
  On Error GoTo lbl_Exit
  Select Case UBound(varIn)
    Case 0: fcnArrayToCommaAndDelimtedList = varIn(0)
    Case 1: fcnArrayToCommaAndDelimtedList = varIn(0) & " and " & varIn(1)
    Case Else
      fcnArrayToCommaAndDelimtedList = varIn(0)
      lngIndex = 1
      Do While lngIndex < UBound(varIn)
        fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", " & varIn(lngIndex)
        lngIndex = lngIndex + 1
      Loop
      If bOxford Then
        fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", and " & varIn(lngIndex)
      Else
        fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & " and " & varIn(lngIndex)
      End If
  End Select
lbl_Exit:
  Exit Function
End Function

[HTSCF Fareha]

Thanks for taking a look at this, Greg. Unfortunately this doesn't work. There are no errors, just a blank ComboBox1 for starters.

Option Explicit

Private Sub EnterBut_Click()
    Dim oTeamMembers As Range
    
    'Check if a team has been selected
    If ComboBox1.ListIndex = 0 Then
        MsgBox "Select Team", vbCritical, "Triage Hub"
        ComboBox1.SetFocus
        Exit Sub
    End If
    
    'Check if team members have been selected
    If TextBox1.Text = "" Then
        MsgBox "Select Team Members", vbCritical, "Triage Hub"
        TextBox1.SetFocus
        Exit Sub
    End If
    
    'use FillBM function to write bookmarks
    FillBM "TeamMembers", TextBox1.Text
    
    Set oTeamMembers = Nothing
    Unload Me
    lbl_Exit
    Exit Sub
    
End Sub

Option Explicit
Dim myArray() As String

Private Sub ComboBox1_Change()
    Select Case ComboBox1.ListIndex
        Case 0
            ListBox1.Clear
            
        Case 1
            'Redefine list for team members Team 1
            myArray = Split("Select Team Members Team 1|Dave|Rob|Sarah|Dave|Rob|Sarah|Liz|Mike", "|")
            ListBox1.List = myArray
        Case 2
            'Redefine list for team members Team 2
            myArray = Split("Select Team Members Team 2|Mike|June|Mary|John|Steve|Maria|Liz|Andy", "|")
            ListBox1.List = myArray
            
        Case 3
            'Redefine list for team members Team 3
            myArray = Split("Select Team Members Team 3|Steve|John|Mary|Ivan|Dan|Lisa|Ian|Joan", "|")
            ListBox1.List = myArray
            
    End Select
    
End Sub

Private Sub ListBox1_Change()
    Dim lngIndex As Long, lngCOunt As Long
    Dim arrTMs() As String
    lngCOunt = 0
    For lngIndex = 1 To ListBox1.ListCount - 1
        If ListBox1.Selected(lngIndex) Then
            ReDim Preserve arrTMs(lngCOunt)
            lngCOunt = lngCOunt + 1
            arrTMs(UBound(arrTMs)) = ListBox1.List(lngIndex)
        End If
    Next lngIndex
    If IsArray(arrTMs) Then TextBox1 = fcnArrayToCommaAndDelimtedList(arrTMs)
End Sub

'**** Create a Comma/And delimited list.
Public Function fcnArrayToCommaAndDelimtedList(varIn As Variant, Optional bOxford As Boolean = False) As String
    Dim strTemp As String
    Dim lngIndex As Long
    On Error GoTo lbl_Exit
    Select Case UBound(varIn)
        Case 0: fcnArrayToCommaAndDelimtedList = varIn(0)
        Case 1: fcnArrayToCommaAndDelimtedList = varIn(0) & " And " & varIn(1)
        Case Else
            fcnArrayToCommaAndDelimtedList = varIn(0)
            lngIndex = 1
            Do While lngIndex < UBound(varIn)
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", " & varIn(lngIndex)
                lngIndex = lngIndex + 1
            Loop
            If bOxford Then
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", And " & varIn(lngIndex)
            Else
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & " And " & varIn(lngIndex)
            End If
    End Select
lbl_Exit:
    Exit Function
End Function

Private Sub FillBM(strbmName As String, strValue As String)
    'Graham Mayor - http://www.gmayor.com
    Dim oRng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        If .Bookmarks.Exists(strbmName) = True Then
            Set oRng = .Bookmarks(strbmName).Range
            oRng.Text = strValue
            oRng.Bookmarks.Add strbmName
        End If
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

[gmaxey]

Your code would not even compile. You have two Option Explicit statements and you have no code to populate the combobox.

[HTSCF Fareha]

I'm embarassed that I had two Option Explicit statements. I'm learning but have been told this before .

I've added a ComboBox to list the three teams, although now I have a 'Variable not defined' here:-

Private Sub ComboBox1_Change()

I'm sure I'm doing something silly to get this error.

Option Explicit

Private Sub EnterBut_Click()
    Dim oTeamMembers As Range
    
    'Check if a team has been selected
    If ComboBox1.ListIndex = 0 Then
        MsgBox "Select Team", vbCritical, "Triage Hub"
        ComboBox1.SetFocus
        Exit Sub
    End If
    
    'Check if team members have been selected
    If TextBox1.Text = "" Then
        MsgBox "Select Team Members", vbCritical, "Triage Hub"
        TextBox1.SetFocus
        Exit Sub
    End If
    
    'use FillBM function to write bookmarks
    FillBM "TeamMembers", TextBox1.Text
    
    Set oTeamMembers = Nothing
    Unload Me
    lbl_Exit
    Exit Sub
    
End Sub

Private Sub ComboBox1_Change()
    Select Case ComboBox1.ListIndex
        Case 0
            ListBox1.Clear
            
        Case 1
            'Redefine list for team members Team 1
            myArray = Split("Select Team Members Team 1|Dave|Rob|Sarah|Dave|Rob|Sarah|Liz|Mike", "|")
            ListBox1.List = myArray
        Case 2
            'Redefine list for team members Team 2
            myArray = Split("Select Team Members Team 2|Mike|June|Mary|John|Steve|Maria|Liz|Andy", "|")
            ListBox1.List = myArray
            
        Case 3
            'Redefine list for team members Team 3
            myArray = Split("Select Team Members Team 3|Steve|John|Mary|Ivan|Dan|Lisa|Ian|Joan", "|")
            ListBox1.List = myArray
            
    End Select
    
End Sub

Private Sub ListBox1_Change()
    Dim lngIndex    As Long, lngCOunt As Long
    Dim arrTMs()    As String
    lngCOunt = 0
    For lngIndex = 1 To ListBox1.ListCount - 1
        If ListBox1.Selected(lngIndex) Then
            ReDim Preserve arrTMs(lngCOunt)
            lngCOunt = lngCOunt + 1
            arrTMs(UBound(arrTMs)) = ListBox1.List(lngIndex)
        End If
    Next lngIndex
    If IsArray(arrTMs) Then TextBox1 = fcnArrayToCommaAndDelimtedList(arrTMs)
End Sub

'**** Create a Comma/And delimited list.
Public Function fcnArrayToCommaAndDelimtedList(varIn As Variant, Optional bOxford As Boolean = False) As String
    Dim strTemp     As String
    Dim lngIndex    As Long
    On Error GoTo lbl_Exit
    Select Case UBound(varIn)
        Case 0: fcnArrayToCommaAndDelimtedList = varIn(0)
        Case 1: fcnArrayToCommaAndDelimtedList = varIn(0) & " And " & varIn(1)
        Case Else
            fcnArrayToCommaAndDelimtedList = varIn(0)
            lngIndex = 1
            Do While lngIndex < UBound(varIn)
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", " & varIn(lngIndex)
                lngIndex = lngIndex + 1
            Loop
            If bOxford Then
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", And " & varIn(lngIndex)
            Else
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & " And " & varIn(lngIndex)
            End If
    End Select
lbl_Exit:
    Exit Function
End Function

Private Sub UserForm_Initialize()
    Dim myArray()   As String
    'Create list of teams
    myArray = Split("- Select -|Team 1|Team 2|Team 3", "|")
    'Use List method to populate ComboBox
    ComboBox1.List = myArray
    ComboBox1.ListIndex = 0
lbl_Exit:
    Exit Sub
End Sub

Private Sub FillBM(strbmName As String, strValue As String)
    'Graham Mayor - http://www.gmayor.com
    Dim oRng        As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        If .Bookmarks.Exists(strbmName) = True Then
            Set oRng = .Bookmarks(strbmName).Range
            oRng.Text = strValue
            oRng.Bookmarks.Add strbmName
        End If
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

[gmaxey]

You might try declaring your myArray variable at the module level like I showed you in my first reply. When you use the Option Explicit statement (once), you "MUST" declare variables.

Option Explicit
Dim myArray() As String

[HTSCF Fareha]

Okay, I've put the variables at the top.

The ComboBox allows the selection of Team 1, Team 2 or Team 3, which populates the ListBox with the correct names for the respective team. But it will only allow for the selection of one of the names (which is transferred to the TextBox), rather than multiple selection.

Pressing the 'Enter' button also gives a compile error - sub or function not defined here:-

Private Sub EnterBut_Click()

Here is my revised code.

Option Explicit

Dim oTeamMembers As Range
Dim myArray() As String

Private Sub ComboBox1_Change()
    Select Case ComboBox1.ListIndex
        Case 0
            ListBox1.Clear
            
        Case 1
            'Redefine list for team members Team 1
            myArray = Split("Dave|Rob|Sarah|Dave|Rob|Sarah|Liz|Mike", "|")
            ListBox1.List = myArray
        Case 2
            'Redefine list for team members Team 2
            myArray = Split("Mike|June|Mary|John|Steve|Maria|Liz|Andy", "|")
            ListBox1.List = myArray
            
        Case 3
            'Redefine list for team members Team 3
            myArray = Split("Steve|John|Mary|Ivan|Dan|Lisa|Ian|Joan", "|")
            ListBox1.List = myArray
            
    End Select
    
End Sub

Private Sub ListBox1_Change()
    Dim lngIndex As Long, lngCOunt As Long
    Dim arrTMs() As String
    lngCOunt = 0
    For lngIndex = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(lngIndex) Then
            ReDim Preserve arrTMs(lngCOunt)
            lngCOunt = lngCOunt + 1
            arrTMs(UBound(arrTMs)) = ListBox1.List(lngIndex)
        End If
    Next lngIndex
    If IsArray(arrTMs) Then TextBox1 = fcnArrayToCommaAndDelimtedList(arrTMs)
End Sub

'**** Create a Comma/And delimited list.
Public Function fcnArrayToCommaAndDelimtedList(varIn As Variant, Optional bOxford As Boolean = False) As String
    Dim strTemp As String
    Dim lngIndex As Long
    On Error GoTo lbl_Exit
    Select Case UBound(varIn)
        Case 0: fcnArrayToCommaAndDelimtedList = varIn(0)
        Case 1: fcnArrayToCommaAndDelimtedList = varIn(0) & " And " & varIn(1)
        Case Else
            fcnArrayToCommaAndDelimtedList = varIn(0)
            lngIndex = 1
            Do While lngIndex < UBound(varIn)
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", " & varIn(lngIndex)
                lngIndex = lngIndex + 1
            Loop
            If bOxford Then
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & ", And " & varIn(lngIndex)
            Else
                fcnArrayToCommaAndDelimtedList = fcnArrayToCommaAndDelimtedList & " And " & varIn(lngIndex)
            End If
    End Select
lbl_Exit:
    Exit Function
End Function

'Enter button
Private Sub EnterBut_Click()
    
    'Check if a team has been selected
    If ComboBox1.ListIndex = 0 Then
        MsgBox "Select Team", vbCritical, "Triage Hub"
        ComboBox1.SetFocus
        Exit Sub
    End If
    
    'Check if team members have been selected
    If TextBox1.Text = "" Then
        MsgBox "Select Team Members", vbCritical, "Triage Hub"
        TextBox1.SetFocus
        Exit Sub
    End If
    
    'use FillBM function to write bookmarks
    FillBM "TeamMembers", TextBox1.Text
    
    Set oTeamMembers = Nothing
    Unload Me
    lbl_Exit
    Exit Sub
    
End Sub

Private Sub UserForm_Initialize()
    Dim myArray() As String
    'Create list of teams
    myArray = Split("- Select -|Team 1|Team 2|Team 3", "|")
    'Use List method to populate ComboBox
    ComboBox1.List = myArray
    ComboBox1.ListIndex = 0
lbl_Exit:
    Exit Sub
End Sub

Private Sub FillBM(strbmName As String, strValue As String)
    'Graham Mayor - http://www.gmayor.com
    Dim oRng As Range
    With ActiveDocument
        On Error GoTo lbl_Exit
        If .Bookmarks.Exists(strbmName) = True Then
            Set oRng = .Bookmarks(strbmName).Range
            oRng.Text = strValue
            oRng.Bookmarks.Add strbmName
        End If
    End With
lbl_Exit:
    Set oRng = Nothing
    Exit Sub
End Sub

[gmaxey]

You have to make the the listbox a multi-select list box.

[HTSCF Fareha]

You have to make the the listbox a multi-select list box.

Doh!

Apologies, this was my fault as I wasn't clear, but I was looking for the ListBox to provide a list of the names rather than as a sentence. The version you have kindly provided will obviously have its uses, although mine needs to populate into a table (two columns).

Is there a way to "store" the names selected from one team so that the list can be added to by selecting names from other teams, without the ListBox starting over again?

Thanks!

[HTSCF Fareha]

I've sorted out my 'Enter' button giving a compile error - sub or function not, but would really appreciate some help re my previous post.

With my limited knowledge I'm guessing that each "group of people" selected from a "team" will need to be stored in either an array or variable? So that these can then somehow be concatenated to populate in the ListBox.

[gmaxey]

What exactly are you trying to do?

[HTSCF Fareha]

There are three teams of people. Selections are required to be made predominantly from one team (duty team), then further people added from either of the other two teams (working as overtime). There should also be an option to add other people who do not appear in any of the three team lists too. These people could come from many other departments so it is completely impracticable to assign them to a fourth list of names.

Once all the personal have been selected, they will be added to the word document at the allotted bookmark. This will need to be as a table that has two columns. The first column with the names of those previously selected, with the second column blank so that the inputter can add any notes against the name to identify say what hours they are working / in the office / working from home etc.

My idea of using a ComboBox for the team selection was purely to try and minimise the amount of screen being taken up at any point, primarily for those using a laptop or tablet. It would be nice to be able to enter all the detail before pressing the 'Enter' button an committing the data to the form.