Hello everyone,
I have a UserForm which allows staff to pick individual workers, choose a job, then enter a quantity. The script will find all matching names that were selected and then sends the quantity to the next open column in that row. The problem is if data is entered more than once a day, usually accidentally. In the example below, someone entered payroll data twice today (25 pieces for all, then 25 for only three workers).
If I include a Date Text Box in the form, is there a way to only send the data to the matching date of the column? So that if someone tried to enter data twice for one day, an error message would pop up, saying something like "Payroll data already exists for the date selected" but only for the workers chosen.
I have a UserForm which allows staff to pick individual workers, choose a job, then enter a quantity. The script will find all matching names that were selected and then sends the quantity to the next open column in that row. The problem is if data is entered more than once a day, usually accidentally. In the example below, someone entered payroll data twice today (25 pieces for all, then 25 for only three workers).
If I include a Date Text Box in the form, is there a way to only send the data to the matching date of the column? So that if someone tried to enter data twice for one day, an error message would pop up, saying something like "Payroll data already exists for the date selected" but only for the workers chosen.
Code:
Private Sub submitButton_Click()
Dim Found As Range
Dim i As Long
Dim msg As String
Dim Check As String
With multiSelectListBox
For i = 0 To .ListCount - 1
If .Selected(i) Then
msg = msg & .List(i) & vbNewLine
End If
Next i
End With
If msg = vbNullString Then
'If nothing was selected, tell user and let them try again
MsgBox "Nothing was selected! Please select an individual(s)!"
Exit Sub
Else
'Ask the user if they are happy with their selection(s)
Check = MsgBox("You selected:" & vbNewLine & msg & vbNewLine & "Hours/Pieces: " & TextBox3.Value & vbNewLine & vbNewLine & _
"Are you happy with your selections?", _
vbYesNo + vbInformation, "Please confirm")
End If
If Check = vbNo Then
'clears data and starts over
For i = 0 To multiSelectListBox.ListCount - 1
multiSelectListBox.Selected(i) = False
Next
End If
'Checks to make sure a job was selected
If IsNull(ComboBox2) Then
MsgBox "No Job Selected!"
Exit Sub
End If
If TextBox3.Value = "" Then
MsgBox "No Quantity Entered!"
Exit Sub
End If
Worksheets(ComboBox2.Value).Activate
With multiSelectListBox
For i = 0 To .ListCount - 1
If .Selected(i) Then
'finds all matching names in worksheet that were selected in the userform
Set Found = ActiveSheet.Range("A2:A37").Find(What:=Me.multiSelectListBox.List(i), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
ActiveSheet.Cells(Found.Row, Columns.Count).End(xlToLeft).Offset(, 1).Value = Me.TextBox3.Value
'clears the listbox
multiSelectListBox.Selected(i) = False
Else
End If
Next i
End With
'Updates image if user updates payroll, then activates that page
If ActiveSheet.Name = "THORLAB - BITES" Then
Call UpdateTableBites
MultiPage1.Value = 0
ElseIf ActiveSheet.Name = "THORLAB - BOXES" Then
Call UpdateTableBoxes
MultiPage1.Value = 1
ElseIf ActiveSheet.Name = "THORLAB - SNACKS" Then
Call UpdateTableSnacks
MultiPage1.Value = 2
ElseIf ActiveSheet.Name = "GLOBAL" Then
Call UpdateTableGlobal
MultiPage1.Value = 3
ElseIf ActiveSheet.Name = "GLOVES" Then
Call UpdateTableGloves
MultiPage1.Value = 4
ElseIf ActiveSheet.Name = "CLEANING" Then
Call UpdateTableCleaning
MultiPage1.Value = 5
ElseIf ActiveSheet.Name = "LAUNDRY" Then
Call UpdateTableLaundry
MultiPage1.Value = 6
ElseIf ActiveSheet.Name = "SHREDDING" Then
Call UpdateTableShredding
MultiPage1.Value = 7
End If
MsgBox "Submitted!"
ComboBox2.Value = ""
TextBox3.Value = ""
End Sub
