User form - select multiple choices in a combobox?

[mnoah]

I cannot thank this board enough! This is my first major post/request. (Many thanks to Rick and AlphaFrog for their contributions to this)

So, I have a userform to record very simple payroll data. The user selects a worker (individual), work performed, and then enters a quantity (hours/pieces) and clicks SUBMIT. The data then gets sent to the matching 'job' worksheet, finds the workers name in column A and places the quantity in the next empty column.

The user can only select ONE worker at a time using a combobox. The combobox pulls in worker names from a list defined in one of the worksheets.

My question: Is it possible for the user to select multiple workers? Since many workers work on the same job at a time, it would be easier to be able to select as many workers worked on that same job. The quantity would be the same. I'm just having a rough time figuring out how to manipulate the VBA to accomplish this if I use checkboxes and how it would populate from the 'names' list. Here is an image of my userform and workbook, code for SUBMIT is below.

Private Sub CommandButton1_Click()
    Dim Found As Range
    
    'If statements check which worksheet to activate and, thus, send data to
    If ComboBox2.Value = "Bites" Then
    Worksheets("ThorLab Bites").Activate
    ElseIf ComboBox2.Value = "Cleaning" Then
    Worksheets("Cleaning").Activate
    ElseIf ComboBox2.Value = "Boxes" Then
    Worksheets("ThorLab Boxes").Activate
    ElseIf ComboBox2.Value = "Snacks" Then
    Worksheets("ThorLab Snacks").Activate
    ElseIf ComboBox2.Value = "Shredding" Then
    Worksheets("Shredding").Activate
    ElseIf ComboBox2.Value = "Global" Then
    Worksheets("Global").Activate
    ElseIf ComboBox2.Value = "Gloves" Then
    Worksheets("Gloves").Activate
    ElseIf ComboBox2.Value = "Laundry" Then
    Worksheets("Laundry").Activate
    
    
    End If
    'finds matching name, then sends data to the right side in the nearest empty cell
    If Me.TextBox3.Value = "" Then
        MsgBox "Nothing entered ", , "Missing Entry"
    Else
        Set Found = ActiveSheet.Range("A:A").Find(What:=Me.ComboBox1.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.ComboBox2.Value, , "No Match Found"
        ElseIf MsgBox("You are about to submit the following pieces/hours for " & Me.ComboBox1.Value & ": " & Me.TextBox3.Value & vbNewLine & vbNewLine & "Is this correct?", vbYesNo, "Is this correct?") = vbNo Then Exit Sub
        Else
            ActiveSheet.Cells(Found.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = Me.TextBox3.Value
        End If
    End If
    Call UserForm_Initialize
    
    
End Sub

 

[MickG]

This might help as a starting point:-

Option Explicit
Dim Temp As String


Private Sub ComboBox1_Click()
    Temp = Temp & "," & ComboBox1.Value
    ComboBox1.Value = Temp
    Range("B1").Value = Mid(ComboBox1.Value, 2)
End Sub


Private Sub ComboBox1_LostFocus()
    ComboBox1.Value = Temp
    Temp = ""
End Sub

 

[mnoah]

This might help as a starting point:-

Option Explicit
Dim Temp As String


Private Sub ComboBox1_Click()
    Temp = Temp & "," & ComboBox1.Value
    ComboBox1.Value = Temp
    Range("B1").Value = Mid(ComboBox1.Value, 2)
End Sub


Private Sub ComboBox1_LostFocus()
    ComboBox1.Value = Temp
    Temp = ""
End Sub

Thanks for your reply, MickG.

I get an "Invalid Property Value" error message when trying to advance to the Work Performed section.

I am wondering now, why don't I just use a ListBox with Multiselect? Then I just need to know how to push the data as it is.

 

[mnoah]

Bump.

Can anyone provide some help on this?

1. User selects multiple workers from a ListBox.
2. User selects ONE job/work performed.
3. User enters quantity, clicks SUBMIT.
4. VBA goes to the job worksheet that the user selected, finds each matching name selected from the ListBox in column A, and sends quantity to the nearest empty column for each name.

I am new to VBA, but I imagine I need language something like "do while" or "for each" matching name.

TIA

 

[mikerickson]

ComboBoxes do not permit multiple selection.
Luckily, ListBoxes do.
If you have a combo box, named myComboBox.
Change its name to temp
Then create a list box and give it the name myComboBox.
Delete the combo box

The rest of the code will act properly, e.g. the new list box will be filled with the proper entries etc. With the exception of the user typing into the combo box, that is not available to a list box.

And you can then adapt it to multi-select (note, the .ListIndex property cannot be used well with a multi-select list box)

Alternatly, you could add an item to the Combobox, "Multiple Employees".
Selecting that would cause a multi-select list box to become visible, etc...

 

[mnoah]

ComboBoxes do not permit multiple selection.
Luckily, ListBoxes do.
If you have a combo box, named myComboBox.
Change its name to temp
Then create a list box and give it the name myComboBox.
Delete the combo box

The rest of the code will act properly, e.g. the new list box will be filled with the proper entries etc. With the exception of the user typing into the combo box, that is not available to a list box.

And you can then adapt it to multi-select (note, the .ListIndex property cannot be used well with a multi-select list box)

Alternatly, you could add an item to the Combobox, "Multiple Employees".
Selecting that would cause a multi-select list box to become visible, etc...

Thanks. I set up the ListBox no problem. However, when I select either one or multiple names, choose a job and enter a quantity, it does not find the matching name in the subsequent job worksheet. It goes to the end of the column A list and enters the data in column B. It does this whether I select one employee or many.

 

[mikerickson]

The .VAlue property of a multi select listbox is basically useless.

Instead of

' do something with MultiSelectListBox.Value

Try something like

With multiSelectListBox
    For i = 0 to .ListCount - 1
        If .Selected(i) Then
            ' do something with .List(i)
        End If
    Next i
End With

 

[mnoah]

Thanks again, but considering my inexperience, I don't know what code to take out or where to insert your suggestion. I've included an update to my submitButton code.

Private Sub submitButton_Click()
    
    
    'If statements check which worksheet to activate and, thus, send data to
    If ComboBox2.Value = "Bites" Then
    Worksheets("Bites").Activate
    ElseIf ComboBox2.Value = "Cleaning" Then
    Worksheets("Cleaning").Activate
    ElseIf ComboBox2.Value = "Boxes" Then
    Worksheets("Boxes").Activate
    ElseIf ComboBox2.Value = "Snacks" Then
    Worksheets("Snacks").Activate
    ElseIf ComboBox2.Value = "Shredding" Then
    Worksheets("Shredding").Activate
    ElseIf ComboBox2.Value = "Global" Then
    Worksheets("Global").Activate
    ElseIf ComboBox2.Value = "Gloves" Then
    Worksheets("Gloves").Activate
    ElseIf ComboBox2.Value = "Laundry" Then
    Worksheets("Laundry").Activate
    
    
    End If
    'finds matching name, then sends data to the right side in the nearest empty cell
    If Me.TextBox3.Value = "" Then
        MsgBox "Nothing entered ", , "Missing Entry"
    Else
        Set Found = ActiveSheet.Range("A:A").Find(What:=Me.multiSelectListBox.Value, _
                                                       LookIn:=xlValues, _
                                                       LookAt:=xlWhole, _
                                                       SearchOrder:=xlByRows, _
                                                       SearchDirection:=xlNext, _
                                                       MatchCase:=False)
        If Found Is Nothing Then
            MsgBox "No match for " & Me.ComboBox2.Value, , "No Match Found"
        ElseIf MsgBox("You are about to submit the following pieces/hours for " & Me.multiSelectListBox.Value & ": " & Me.TextBox3.Value & vbNewLine & vbNewLine & "Is this correct?", vbYesNo, "Is this correct?") = vbNo Then Exit Sub
        Else
            ActiveSheet.Cells(Found.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = Me.TextBox3.Value
        End If
    End If
    Call UserForm_Initialize
    
    
End Sub

 

[My Aswer Is This]

Just one little bit of advice. Instead of this long list of if statements you could just use my one line script:

If ComboBox2.Value = "Bites" Then
    Worksheets("Bites").Activate
    ElseIf ComboBox2.Value = "Cleaning" Then
    Worksheets("Cleaning").Activate
    ElseIf ComboBox2.Value = "Boxes" Then
    Worksheets("Boxes").Activate
    ElseIf ComboBox2.Value = "Snacks" Then
    Worksheets("Snacks").Activate
    ElseIf ComboBox2.Value = "Shredding" Then
    Worksheets("Shredding").Activate
    ElseIf ComboBox2.Value = "Global" Then
    Worksheets("Global").Activate
    ElseIf ComboBox2.Value = "Gloves" Then
    Worksheets("Gloves").Activate
    ElseIf ComboBox2.Value = "Laundry" Then
    Worksheets("Laundry").Activate
    
    
    End If

[COLOR="#FF0000"]Use this one line of script[/COLOR]

Worksheets(ComboBox2.Value).Activate

 

[mnoah]

Thanks for the tip! Can anyone suggest how to insert mikerickson's suggestion?

Just one little bit of advice. Instead of this long list of if statements you could just use my one line script:

If ComboBox2.Value = "Bites" Then
    Worksheets("Bites").Activate
    ElseIf ComboBox2.Value = "Cleaning" Then
    Worksheets("Cleaning").Activate
    ElseIf ComboBox2.Value = "Boxes" Then
    Worksheets("Boxes").Activate
    ElseIf ComboBox2.Value = "Snacks" Then
    Worksheets("Snacks").Activate
    ElseIf ComboBox2.Value = "Shredding" Then
    Worksheets("Shredding").Activate
    ElseIf ComboBox2.Value = "Global" Then
    Worksheets("Global").Activate
    ElseIf ComboBox2.Value = "Gloves" Then
    Worksheets("Gloves").Activate
    ElseIf ComboBox2.Value = "Laundry" Then
    Worksheets("Laundry").Activate
    
    
    End If

[COLOR=#FF0000]Use this one line of script[/COLOR]

Worksheets(ComboBox2.Value).Activate