Trying to have Text Box removed after a 5 second delay

[mcomp72]

I am wanting a text box to appear if the user moves the mouse over a certain cell (E18) on my worksheet, and then I want that text box to disappear after 5 seconds. I found a blog post that talked about how to do the first part of this:
https://optionexplicitvba.blogspot.com/2011/04/rollover-b8-ov1.html

Using what the author wrote on the blog, I have gotten the text box to appear when I mouse over the cell.

I've tried to add the code to remove the text box after 5 seconds, but nothing I have tried is working. Here is all of my code. (All of it is inside Module3).

Public Function MouseOverForRedTextBox()

With ThisWorkbook.Sheets("Sheet1")

    If .Range("N18").Value <> "RedTextBox" Then
    
        'THIS CALLS THE SUB TO CREATE THE TEXT BOX
        CreateRedTextBox
        
        'NOW I WANT THE TEXT BOX TO BE REMOVED 5 SECONDS AFTER IT WAS CREATED
        'NONE OF THE THREE THINGS I TRIED HAVE WORKED.  THE RemoveRedTextBox SUB DOESN'T SEEM TO RUN
        
        'Attempt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
        Dim WaitTime As Date
        WaitTime = Now + TimeValue("00:00:05")
        Application.OnTime WaitTime, "Module3.RemoveRedTextBox"
        
        'Attempt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=2]#2[/URL] 
        Application.OnTime DateAdd("s", 5, Time), "Module3.RemoveRedTextBox"
        
        'Attempt [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=3]#3[/URL] 
        Application.Wait (Now + TimeValue("0:00:05"))
        RemoveRedTextBox
    
    End If

End With

End Function

Sub CreateRedTextBox()

Dim txtBox As Shape
Dim txBoxTop As Integer
Dim txBoxLeft As Integer
Dim txBoxName As String

txBoxName = "RedTextBox"

With ThisWorkbook.Sheets("Sheet1")

    txBoxTop = .Range("E18").Top
    txBoxLeft = .Range("E18").Left
    
    .Shapes.AddTextbox(msoTextOrientationHorizontal, txBoxLeft, txBoxTop, 40, 40).Name = txBoxName
    
    Set txtBox = .Shapes("RedTextBox")

    txtBox.TextFrame.Characters.Text = "RED"
    txtBox.TextFrame.HorizontalAlignment = xlCenter
    txtBox.TextFrame.VerticalAlignment = xlCenter
    txtBox.TextFrame.Characters.Font.Name = "Arial"
    txtBox.TextFrame.Characters.Font.FontStyle = "Bold"
    txtBox.TextFrame.Characters.Font.Underline = xlUnderlineStyleSingle
    
    'this writes the name of the text box in cell N18
    .Range("N18").Value = txBoxName
    
    Set txtBox = Nothing

End With
 
End Sub

Sub RemoveRedTextBox()

With ThisWorkbook.Sheets("Sheet1")

    .Shapes("RedTextBox").Delete
    .Range("N18").ClearContents

End With

End Sub

This is the formula in cell E18:

=IFERROR(HYPERLINK(MouseOverForRedTextBox(),""), "")

If I run the RemoveRedTextBox sub manually, it works fine, so I know the code itself works. But for whatever reason, none of my attempts to get it to run 5 seconds later seems to be working. Any ideas what I am doing wrong?

 

[JLGWhiz]

Try this

Public Function MouseOverForRedTextBox()
Dim s As Long
With ThisWorkbook.Sheets("Sheet1")
    If .Range("N18").Value <> "RedTextBox" Then    
        'THIS CALLS THE SUB TO CREATE THE TEXT BOX
        CreateRedTextBox        
        'NOW I WANT THE TEXT BOX TO BE REMOVED 5 SECONDS AFTER IT WAS CREATED
        'NONE OF THE THREE THINGS I TRIED HAVE WORKED.  THE RemoveRedTextBox SUB DOESN'T SEEM TO RUN
        s = Timer + 5
        Do While Timer < s
            DoEvents
        Loop        
        RemoveRedTextBox   
    End If
End With
End Function

 

[mcomp72]

That worked. Thanks!

Just for future reference, any idea why the other options I tried did not work?

 

[JLGWhiz]

You're welcome. I didn't do a lot of analysis on the code you tried, but it was probably the time that was not allowing the variable to initialize.
Regards, jlg