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