Solved: Message box alert if cells not filled in on time

[BENSON]

I have a spreadsheet called:"Temperature Check" the user is supposed to enter the running temperature of 5 pieces of equipment at specific times of the day 10:00, 14:00, 17:00 is it possible to have a message box alert if the user has not entered the data by say five minuites after the set times. The temperature are recorded in collums "C,D,E"

Thanks

[Bob Phillips]

This sounds like a job for Intime, but a bit more detail is required.

Will the data always be in the same cells, or will it be in a new row appended to the existing? If the latter, how will you know that it has been entered at the appropriate time slot?

[BENSON]

THANKS FOR THE REPLY ,The cells will always be the same in each collum "C6 >C10","D6>d10"and "E6>E10.A new worksheet will be used each day

THANKS

[mikerickson]

What is the name of today's worksheet?

[Bob Phillips]

In a standard module add



Option Explicit

Public mgNextRun As Double

Public Sub NextCheck()

With Worksheets("TimeCheck")
If Application.CountA(.Range("C6:E10")) <> .Range("C6:E10").Cells.Count Then

MsgBox "Data incomplete"
End If
End With

Select Case True

Case Time < TimeSerial(10, 5, 0)

mgNextRun = TimeSerial(10, 5, 0)
Case Time < TimeSerial(14, 5, 0)

mgNextRun = TimeSerial(14, 5, 0)
Case Time < TimeSerial(17, 5, 0)

mgNextRun = TimeSerial(17, 5, 0)
Case Else

mgNextRun = Date + 1 + TimeSerial(10, 5, 0)
End Select

Application.OnTime mgNextRun, "NextCheck"
End Sub


In ThisWorkbook, add



Private Sub Workbook_Open()
Call NextCheck
End Sub

[BENSON]

The spreadsheet opens each day in the excel start folder,it is called "Check List" followed by the date so today would be "Check List 7 JAN 08"

[BENSON]

The code supplied does work when the spread sheets opens for the first time ,However is it not possible that if the worksheet is opened at say 8:00 and remains open and the time has passed for the 1st entry to be done then the message box appears .

THANKS

[Bob Phillips]

The original message is a logic error, but the subsequent checks should happen. ARe you finding thata they don't?

[BENSON]

THANKS