Excel VBA: Make text blink in status bar: Excel, VBA

Excel VBA: Make text blink in status bar

Hi All,

I was wondering if anyone knew how to make text passed to the Application.StatusBar property blink in the Excel status bar? For example something like: Application.StatusBar = "Alert!".

In addition, does anyone know how to make the text scroll repeatedly across the status bar?

Thanks

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation

Collaborate with the world's top tech experts

Unlimited access to our exclusive solution database

Never be left without tech help again

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.

"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin

"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

I hope that you aren't displaying a flashing message because your macro takes a long time to run. If so, a completely different approach is required--because the macro must finish before the OnTime event (which makes things blink) triggers. Your best approach is to make your macro run faster--if you recorded it, I guarantee that you can make the code run faster by eliminating the useless fluff that the macro recorder records. Your next best approach is to sprinkle statements in the macro that change the status bar text.

For blinking status bar text, about the best that you can do is to use the Application.OnTime event to turn the status bar text on and off. As you call the OnTime event repeatedly, one call clears the text and the next call displays it. The text is set in the AlarmClock sub by the variable Msg.

Although the sample code is adequate for most purposes, you may find yourself needing to pass a parameter to the macro called by the OnTime event (such as the text of the message). If so, Dick Kuskleika gives instructions at http://www.dicks-blog.com/archives/2004/06/03/passing-arguments-through-onaction/

You could use the same approach to simulate scrolling, but the results won't be very good in my opinion. Either you spend too many machine cycles calling the OnTime event sub, or the refresh is so slow that the results are extremely jerky.

Note that some of the code goes in ThisWorkbook code pane, while other bits must go in a regular module sheet. The code won't work at all if you don't put it in the right place.

Brad

Private Sub Workbook_Open()    'Code goes in ThisWorkbook code pane. It won't work if installed anywhere else!
RunWhen = Now + 1 / 86400      'Run every 1 seconds. With intervals this short, use a Boolean switch to stop the timer
StartTimer
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)    'Code goes in ThisWorkbook code pane
StopTimer   'Turn the timer off when you close the workbook. If you don't do this, workbook will be reopened to run AlarmClock.
End Sub
 
 
 
'Code goes in regular module sheet, with Public declarations before any subs or functions.
Public RunWhen As Double       'Put these two statements above all subs and functions!
Public Const cRunWhat = "AlarmClock"
 
Sub StartTimer()      'Code goes in regular module sheet
'The next five statements create a Boolean switch to control whether the timer runs.
Dim rg As Range
On Error Resume Next
Set rg = Range("UseMyTimer")
If rg Is Nothing Then ActiveWorkbook.Names.Add "UseMyTimer", "=TRUE"
On Error GoTo 0
 
'You need to pick the next time to run the AlarmClock sub. Use one of the following statements customized to your situation
RunWhen = Now + 1 / 86400      'Run every second. With intervals this short, use a Boolean switch to stop the timer
 
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=True
End Sub
 
 
Sub AlarmClock()        'Code goes in regular module sheet
Dim Msg As String
Msg = "This is a blinking status bar"
If Application.DisplayStatusBar = False Then Application.DisplayStatusBar = True
Application.StatusBar = IIf(Application.StatusBar = " ", Msg, " ")
 
If [UseMyTimer] Then StartTimer  'Use this construction if time interval between calls is short (say < 10 seconds)
End Sub
 
 
Sub StopTimer()         'Code goes in regular module sheet
On Error Resume Next
Range("UseMyTimer") = False 'Use this statement if time interval between calls is short
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, Schedule:=False
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:

Select all Open in new window

BlinkingStatusBarQ24304110.xls
- 25 KB
Sample workbook showing blinking status bar

Question

Excel VBA: Make text blink in status bar

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Excel

VBA

Microsoft Excel Spreadsheet Software

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press