Selecting a team from a ListBox
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.
Code:
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