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