Reputation: 9548
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
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