CODEWITHSUNDEEP
excelvba
jjacker
jjacker

Reputation: 21

Run event whenever date changes in a cell A1

Need your input on how to run an Event whenever the date on cell A1 changes. The current formula in cell A1 is =Today().

This code doesn't work on me:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Target.Address = "$A$1" Then

    MsgBox "Another day has come!"

  End If

End Sub

Ex. What will happen should be, if it's 11:59 PM (Jun 11, 2019) and goes to the next day, 12:00 AM (Jun 12, 2019) a procedure will automatically run.

Thanks guys.

Upvotes: 2

Views: 84

Answers (2)

EvR
EvR

Reputation: 3498

Another possibility, use Application.ontime to run every day:

Public myBool As Boolean
Sub StartNextday()
    If myBool Then MsgBox "Another day has come!"
        Application.OnTime TimeSerial(0, 0, 0), "StartNextday"
        myBool = True
End Sub
Sub CloseNextday()
        Application.OnTime TimeSerial(0, 0, 0), "StartNextday", , False
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96781

I would use B1 as a "memory" cell. Using BigBen's suggestion:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
        If [B1] = "" Then
            [B1] = [A1]
        ElseIf [B1] <> [A1] Then
            [B1] = [A1]
            MsgBox "Another day has come!"
        End If
    Application.EnableEvents = True
End Sub

Upvotes: 2

Related Questions