• 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......

B.
Macro-for-Displaying-Text-in-a-Cell.xlsm
0
Bright01
Asked:
Bright01
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
Do
Loop Until Timer > iOff
rShow.Interior.Color = RGB(255, 255, 255)
rShow = ""


End Sub

Open in new window

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

B.
1
 
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:

rshow.interior.colorIndex=0

Open in new window

1
 
Roy CoxGroup Finance ManagerCommented:
Why not just use a message box at the end of the code? Much simpler in my opinion.
0
 
Rob HensonFinance AnalystCommented:
Maybe the formatting could be Conditional based on the text entry. The default formatting won't then need resetting.
1
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