Trying to have Text Box removed after a 5 second delay

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
170
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
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).

Code:
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:
Code:
=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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try this

Code:
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
 
Last edited:

mcomp72

Board Regular
Joined
Aug 14, 2016
Messages
170
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2011
Platform
  1. Windows
  2. MacOS
That worked. Thanks! :cool:

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

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,126,992
Messages
5,622,029
Members
415,874
Latest member
JockPC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top