VBA-Excel : Conditional message box popup every 2 minutes from NOW

Question

I have been trying to work things out with NOW (Date/time) but have been unsuccessful.

1. Checkbox1 when True captures NOW in textbox1.

2. From the value in textbox1, every 2 minutes i need a popup message reminder with OK as acknowledgement until checkbox2 is True. Can someone help with this logic and code? Thanks.

   Dim X1 As Date
   Dim X2 As Date
   Dim X3 As Date
   Dim X4 As Date
   
   Private Sub CheckBox1_Click()
   
   X1 = Now
   
   If CheckBox1.Value = True Then TextBox1.Value = Now
   
   Call function01
   
   If CheckBox1.Value = False Then TextBox1.Value = Null
   
   End Sub
   
   Sub function01()
   X2 = "00:02:00"
   X3 = Now - X1
   X4 = Format(X3, "hh:mm:ss")
       If CheckBox1.Value = True Then
   
            If X4 = X2 Then
             MsgBox "Prompt", vbOKOnly, "Time Keeper Tool Reminder"
            Else
   
            End If
       Else
       End If
   End Sub
   

Answer 1

You can use the OnTime event rather than a loop.

Change your code to:

If CheckBox1.Value = True Then
    TextBox1.Value = Now
    schedulePopup
End If

And to a Module add:

Public Sub schedulePopup()
    '// schedule popup
    Application.OnTime Now + TimeValue("00:00:20"), "popupMessage", , True
End Sub

Public Sub popupMessage()
    If Not YourUserForm Is Nothing Then
        If YourUserForm.CheckBox1.Value = True Then
            MsgBox "Time Keeper Tool Reminder"
            '// schedule next
            schedulePopup
        End If
    End If
End Sub