VBA to check if all names in a list have been processed

[boatbabe]

Hi, I have a payroll spreadsheet and we have had instances where employees have not been processed for a week because they have been on holiday and so have not submitted a timesheet. I therefore want to put in some code to run when the workbook is shut to check if all the names in the 'Lists' table have an entry against them on the 'Data' sheet for the week in the last row. My data that looks like this:

Lists worksheet

A

Mr A

Mr B

Mr C

<tbody>
</tbody>

Data worksheet

C	D
Mr A	1/5/18
Mr B	1/5/18
Mr C	1/5/18
Mr A	8/5/18
Mr C	8/5/18

<tbody>
</tbody>

On closing the workbook the macro should find the date in column D of the last row, and check that all employees listed in column A on the Lists tab have an entry with that date on the data tab - in the above Mr B doesn't ahve an entry for the 8th of May and so would display a message box saying that.

If anyone has 5 minutes and can help would be very grateful,
Thanks

 

[boatbabe]

This is what I have so far but not sure how to structure the Loop that I assume is now needed

Dim ProcDate As Date
    Dim LastRow As Long
    
    Sheets("Data").Select
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        ProcDate = .Range("D" & LastRow)
    End With

 

[Scott T]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wslist As Worksheet
Dim wsdata As Worksheet
Dim lrl As Long
Dim lrd As Long
Dim lastdate As Date
Dim tf As Boolean
Set wslist = Sheets("lists") 'change to the name of your sheet
Set wsdata = Sheets("data") 'change to the name of your sheet
lrl = wslist.Cells(Rows.Count, 1).End(xlUp).Row
lrd = wsdata.Cells(Rows.Count, 3).End(xlUp).Row
lastdate = wsdata.Cells(lrd, 4)

For x = 2 To lrl
    For y = 2 To lrd
        If UCase(wslist.Cells(x, 1)) = UCase(wsdata.Cells(y, 3)) And wsdata.Cells(y, 4) = lastdate Then
            tf = True
            Exit For
        Else
            tf = False
        End If
    Next y
    
    If tf = False Then MsgBox (wslist.Cells(x, 1) & " not processed")

Next x
 
End Sub

 

[hotabae]

In case you are interested, I added a bit an to the end of #3 (if an employee didn't meet the procDate requirement, it'll allow you to stop the "Closing" process)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wslist As Worksheet
Dim wsdata As Worksheet

Dim lrl As Long
Dim lrd As Long

Dim lastdate As Date
Dim tf As Boolean

Set wslist = Sheets("lists") 'change to the name of your sheet
Set wsdata = Sheets("data") 'change to the name of your sheet

lrl = wslist.Cells(Rows.Count, 1).End(xlUp).Row
lrd = wsdata.Cells(Rows.Count, 3).End(xlUp).Row

lastdate = wsdata.Cells(lrd, 4)

For x = 2 To lrl
    For y = 2 To lrd
        If UCase(wslist.Cells(x, 1)) = UCase(wsdata.Cells(y, 3)) And wsdata.Cells(y, 4) = lastdate Then
            tf = True
            Exit For
        Else
            tf = False
        End If
    Next y
    
    If tf = False Then[INDENT][COLOR=#ff0000]MsgBox (wslist.Cells(x, 1) & " not processed")
[/COLOR][COLOR=#ff0000]Cancel = true
[/COLOR][/INDENT]
[COLOR=#ff0000]    End If[/COLOR]


Next x
 
[COLOR=#ff0000]Dim closecheck As VbMsgBoxResult

If Cancel = True Then

[/COLOR][INDENT][COLOR=#ff0000]closecheck = MsgBox("Employees have not been processed, do you still wish to close?", vbYesNo)

If closecheck = vbYes Then

[/COLOR][/INDENT]
[INDENT=2][COLOR=#ff0000]Cancel = False[/COLOR][/INDENT]
[INDENT][COLOR=#ff0000]
End If[/COLOR][/INDENT]
[COLOR=#ff0000]
End If[/COLOR]

End Sub

 

[boatbabe]

Scott T that works perfectly, thank you so much!

Hotabae you read my mind because as soon as I tested it I realised it needed that and was going to add it!

Thanks both