Reset Cell Value to Zero every day (after midnight)

[mykal66]

I have a workbook that has a cell that’s counts the number of times it is opened each day but need to reset the value to zero the first time it is opened after midnight.

In short the count is used to trigger an automatic email reminder but I only want it to send once a day so when the count is 1 it will send the email but when it increases to above 1 no more emails will be sent until the following day.

The count works fine but I have no idea how to the value in C5 back to zero each day.

Can anybody help please? I have uploaded an example workbook.

Thank you

Mykal

[p45cal]

Instead of counting, store the date (and time if you want) of the last time an email was sent. Use the email sending routine to do this (Sheets("Admin").range("C3")=date). Thereafter, every time the file is opened, compare the (integer portion of the) stored date with Date (vba for system date) and if the difference is greater than 0, send an email (and update the stored date)(If Date-Sheets("Admin").range("C3").value>0 then…)).

[Paul_Hossler]

Something like this in the Workbook module could probably work




Option Explicit
Private Sub Wworkbook_Open()

Dim LastDateSent As Date

On Error GoTo FirstTIme
LastDateSent = ThisWorkbook.CustomDocumentProperties("EmailSentDate")
On Error GoTo 0

If LastDateSent < Int(Now) Then
MsgBox "Send email" ' logic
ThisWorkbook.CustomDocumentProperties("EmailSentDate") = Int(Now)
End If

Exit Sub

FirstTIme:
Call ThisWorkbook.CustomDocumentProperties.Add("EmailSentDate", False, msoPropertyTypeDate, Int(Now) - 1) ' yesterday
Resume
End Sub

[mykal66]

Hi both
Sorry, I have been off work ill so only just come back intowork today and not had chance to try either solution as yet but wanted to saythank you to you both for your help.
Mykal