Excel VBA - Removing Text After Some Time

user_8583260 0
Viewed 245

I'm trying to show some hint as text in cell for a few seconds as below.

    Dim text As String
    text = "Hello World"

    If Range("A1") = 1 Then 
        Range("B1") = text 
        Application.Wait (Now + TimeValue("0:00:05"))
        Range("B1") = ""
    End If

The code runs perfectly if the vba has only the above code but for my case, there is a big chunk of codes before this "Hint" function. Whenever the IF statement turns TRUE, my mouse cursor would turn into window's loading sign without cell B1 showing anything.

After 5 sec, "Hello World" would appears very briefly before cell B1 becomes empty again, just as what it was intended to do, but with the timing all messed up.

I know the hardware may be at fault here but is there any other way to go around this?

vba excel
3 Answers

It's difficult to diagnose your specific issue without seeing the above coding, but you can try something along these lines:

Sub Test()

    Dim text As String, iLoop As Integer
    text = "Hello World"

    If Range("A1") = 1 Then
        For iLoop = 1 To 5  '5 is the number of seconds to wait
            Range("B1") = text
            DoEvents
            Application.Wait (Now + TimeValue("0:00:01"))
        Loop
        Range("B1") = ""
    End If

End Sub
user_57817450

A possible solution is to deal with events - in the worksheet, like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

    If Range("A1") = 1 Then
        Range("B1") = "Hello world"
        Application.Wait (Now + TimeValue("0:00:05"))
        Range("B1") = vbNullString
    End If

End Sub

Make sure that you have Application.EnableEvents = True, in oder to see the difference.

user_54486260

As the Application.Wait eats up the CPU to 100% ("busy wait"), it's better to use the Windows Sleep.

You have to put a declaration at the top of the code:

Private Declare Sub Sleep Lib "kernel32" (ByVal lngMilliSeconds As Long)

Then, write a simple Sub like this:

Public Sub WaitSeconds(seconds As Integer)
    Dim WaitUntil As Date
    WaitUntil = DateAdd("s", seconds, Now)

    Do While Now < WaitUntil
        Sleep 100
        DoEvents
    Loop
End Sub
user_75997980
Related