Userform - Find Matching Date in column then send data? If data exists, error message?

mnoah

Board Regular
Joined
Oct 14, 2015
Messages
54
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.




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
 

Forum statistics

Threads
1,081,911
Messages
5,362,034
Members
400,668
Latest member
seacubs17

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top