Punch-clock with Employee # AND Work Order # input

[mattayom]

Hey everyone!
I'd like to start off by saying how much of a help this forum has been, I've been lurking for a while but now I need some custom tailored help.

I need to create a punch clock spreadsheet. Now I know there's a ton out there and I've looked at/tried out many different "punch-clock" style workbooks, but have yet to find something that fits my needs.

What I need:
A workbook that when opened displays a User Form, the user form will have two fields, one for Employee #, and one for Work Order #, and a "Punch-In" and "Punch-Out" button. (If it would be easier to do this with a single button then that's fine.) The user will then scan their badge and work order with a barcode scanner (already have this part figured out) and click either "punch-in" or "punch-out".
My idea is to have a single sheet with 5 columns, A)Order#, B)Employee#, C)Time-In, D)Time-Out, E) Hours.

What I need the VB to do is:
If the user clicks "punch-in", search for the order#, and find all rows with that order # (Column A). Then from those matches, check to see if the scanned employee # exists in column B. If the employee has never clocked on to that work order, populate a new row containing the order #(A), employee #(B), and a punch-in time(Column C) using the NOW() function. If the employee has already clocked on the order, but not out, then display a message box saying "You've already clocked on to that order". If the employee has clocked in AND out of that order, then populate a new line as if they've never clocked in before.
If the user clicks "punch-out", same idea as above, but reversed. If they're not clocked-in, display a message, if they are clocked in put time out in column D for the matching row.

Now all that being said, If anyone has suggestions to make it easier to accomplish like using multiple sheets, or structuring the form differently, I'm fine with it, as long as I'm still able to capture what I need. I've attempted to do this myself by using & manipulating code that I've found, but I couldn't get it to work correctly. I'll link threads I've used and below is my currently not-even-close-to-working mutilated script.

https://www.excelforum.com/excel-programming-vba-macros/978552-excel-time-clock.html
https://www.ozgrid.com/forum/forum/tip-tricks-code/109765-basic-employee-punch-clock
https://www.ozgrid.com/forum/forum/...-scheduling-worksheet-with-punch-in-out-times
https://www.mrexcel.com/forum/excel-questions/453313-find-value-userform-textbox-cell.html

Private Sub CommandButton1_Click()


DT = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM ")


Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'DMO Input
Dim Inp1
'Employee Number Input
Dim Inp2
Dim DMORng As Range
Dim EmpRng As Range
Dim TimeIn As Range
Dim TimeOut As Range
Inp1 = DMO.Value
Inp2 = EmpNum.Value
    'Search Column A for DMO#
    With Sheets("Sheet1").Range("A:A")
'    On Error GoTo NewLine:
        Set DMORng = .Find(what:=Inp1, after:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
Debug.Print DMORng


    End With
    
    
    
    'Search DMORng for Employee #
    With Sheets("Sheet1").Range(DMORng)
        Set EmpRng = .Find(what:=Inp2, after:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
    End With
    'Search EmpRng for Dates
    With Sheets("Sheet1").Range(EmpRng)
        Set CIDate = .Find(what:="*", after:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
    End With
    


    
'NewLine:


'TRY USING COUTIFS?


        'If DMO# is found
        If Not DMORng Is Nothing Then
            'And if Employee# is found
            If Not EmpRng Is Nothing Then
                'And if Clock-In is found
'                If Not IsEmpty(EmpRng.Offset(, 1)) Then
                If Not CIDate Is Nothing Then
                    'And if Clock-Out is found
                    If Not IsEmpty(EmpRng.Offset(, 2)) Then
                        lngWriteRow = ws.Cells(Rows.Count, 2) _
                        .End(xlUp).Offset(1, 0).Row
                        ws.Range("A" & lngWriteRow) = DMO.Value
                        ws.Range("B" & lngWriteRow) = EmpNum.Value
                        ws.Range("C" & lngWriteRow) = DT
                    'If Clock-Out is not found
                    Else
                        'Warning box
                        i = MsgBox("You are already clocked in to that DMO", vbOKOnly + vbCritical)
                    End If
                'If Clock-In is NOT found
                Else
                    lngWriteRow = ws.Cells(Rows.Count, 2) _
                    .End(xlUp).Offset(1, 0).Row
                    ws.Range("C" & lngWriteRow) = DT
                End If
            'If Emp# is NOT found
            Else
                lngWriteRow = ws.Cells(Rows.Count, 2) _
                .End(xlUp).Offset(1, 0).Row
                ws.Range("A" & lngWriteRow) = DMO.Value
                ws.Range("B" & lngWriteRow) = EmpNum.Value
                ws.Range("C" & lngWriteRow) = DT
            End If
        'If DMO is NOT found
        Else
            lngWriteRow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row
            ws.Range("A" & lngWriteRow) = DMO.Value
            ws.Range("B" & lngWriteRow) = EmpNum.Value
            ws.Range("C" & lngWriteRow) = DT
        End If
                
    


'Clear Form
DMO.Value = ""
EmpNum.Value = ""


End Sub




Private Sub CommandButton2_Click()


DT = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM ")


Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
'DMO Input
Dim Inp1
'Employee Number Input
Dim Inp2
Dim DMORng As Range
Dim EmpRng As Range
Dim CIDate As Range
Inp1 = DMO.Value
Inp2 = EmpNum.Value


    'Search Column A for DMO#
    With Sheets("Sheet1").Range("A:A")
        Set DMORng = .Find(what:=Inp1, after:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
    End With
    
    'Search Column B for Employee #
    With Sheets("Sheet1").Range("B:B")
        Set EmpRng = .Find(what:=Inp2, after:=.Cells(.Rows.Count, 1), LookIn:=xlValues, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
        'Set CIDate = Range(EmpRng.Offset(, 1))
    End With
    
    'If DMO# is found
        If Not DMORng Is Nothing Then
            'And if Employee# is found
            If Not EmpRng Is Nothing Then
                'And if Clock-In is empty
                If IsEmpty(EmpRng.Offset(, 1)) Then
                    'Warning box
                    i = MsgBox("You are not clocked in to that DMO", vbOKOnly + vbCritical)
                Else
                    'Insert timestamp in Column D
                    DMORng.Offset(0, 3).Value = DT
                End If
            'If DMO# is found but Employee# is not found
            Else
                lngWriteRow = ws.Cells(Rows.Count, 2) _
                .End(xlUp).Offset(1, 0).Row
                ws.Range("A" & lngWriteRow) = DMO.Value
                ws.Range("B" & lngWriteRow) = EmpNum.Value
                ws.Range("C" & lngWriteRow) = DT
            End If
        'If DMO and Employee# are not found
        Else
            lngWriteRow = ws.Cells(Rows.Count, 2) _
            .End(xlUp).Offset(1, 0).Row
                ws.Range("A" & lngWriteRow) = DMO.Value
                ws.Range("B" & lngWriteRow) = EmpNum.Value
                ws.Range("C" & lngWriteRow) = DT
        End If
    


'Clear Form
DMO.Value = ""
EmpNum.Value = ""


End Sub

Thanks!

 

[mattayom]

Bump!

 

[mattayom]

2nd bump.................

 

[mattayom]

3rd