• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 42
  • Last Modified:

Display and Disappear Text in Cell

EE Pros,

I am looking for some help in building a macro that does something very simple.  It posts a text confirmation in a cell, to be displayed for 4 seconds, black text, yellow fill, then disappear.  I plan to call the sub from other routines at the end as a confirmation of the sub being completed.

I have mocked up a sample for you.

Thank you in advance......

1 Solution
Neil FlemingIndependent consultantCommented:
I'd use the "Timer" function for this. Try the following:

Option Explicit

Sub testflash()
'routine to test the flashtext routine below:
flashText "test", Range("c5"), 4

End Sub

Sub flashText(sText As String, rShow As Range, iShowFor As Long)
Dim iOff As Long
'set the off time. the built-in Timer gives the number of seconds since midnight
iOff = Timer + iShowFor
rShow.Interior.Color = RGB(255, 255, 0)
rShow = sText

'loop till the specified number of seconds has passed
Loop Until Timer > iOff
rShow.Interior.Color = RGB(255, 255, 255)
rShow = ""

End Sub

Open in new window

Bright01Author Commented:
Neil,  Great job!  Quick and right to the point.  Appreciate your help with this.

Neil FlemingIndependent consultantCommented:
Occurs to me that I reset the cell to white. You may want to put it back as an zero-colour cell, in which case you'd need:


Open in new window

Roy CoxGroup Finance ManagerCommented:
Why not just use a message box at the end of the code? Much simpler in my opinion.
Rob HensonFinance AnalystCommented:
Maybe the formatting could be Conditional based on the text entry. The default formatting won't then need resetting.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now