YasserKhalil
YasserKhalil

Reputation: 9548

Clear cell each 10 seconds in worksheet change

In the following code I am trying to make the cell A1 empty each 10 seconds and at the same time to be able to work on the other cells or even any other worksheet without interrupt.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Date

If Target.Address = "$A$1" Then
    t = Now() + TimeSerial(0, 0, 10)
    Do

        If Now() >= t Then
        Application.EnableEvents = False
            Range("A1").ClearContents
            Application.EnableEvents = True
            Exit Do
            End If
        DoEvents
    Loop
End If
End Sub

The code may work and clear the cell contents but sometimes I found that it is not cleared and filled with the value entered in another cell. Any idea how to fix that?

Upvotes: 0

Views: 305

Answers (1)

Tim Williams
Tim Williams

Reputation: 166351

In the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        'existing call to clear?
        If scheduled > 0 Then Application.OnTime scheduled, "ClearIt", schedule:=False
        scheduled = Now + TimeSerial(0, 0, 5)
        Application.OnTime scheduled, "ClearIt", schedule:=True
    End If
End Sub

In a regular module:

Public scheduled

Sub ClearIt()
    Application.EnableEvents = False
    Sheet1.Range("A1").Value = ""
    Application.EnableEvents = True
    scheduled = 0
End Sub

Upvotes: 1

Related Questions