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,084,753
Messages
5,379,657
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top