Show a progress bar on the status bar? (Excel 2000 >)

Topic

New Reply

Hi all,
I know it is possible to show a counter on the status bar when a lengthy macro executes, by setting up code such as : Application.StatusBar = “Processing row ” & myRow & ” of ” & myLastRow.
The variables are counters in a loop!
Is it possible to have a progress bar display on the status bar of stead of this text based counter.?

Reply | Quote

Replies

Hi Rudi

Check Daily Dose of Excel for an example.
It also includes links to other progress bars.

Reply | Quote

Beautiful…this will do great!
Thanx Tony!

Reply | Quote

I also thank you, Tony, as I was just now looking to create a status bar. Rudi, if you want a filled box as the progress indicator, try using ChrW(9632) (ChrW gets Unicode characters) instead of Chr(31).

Reply | Quote

I’ll give it a go John. I’ve not played around with this before…so its quite new to me. I have a lot of experimentation to do.
Thanx for that tip!

Reply | Quote

I’ll give it a go John. I’ve not played around with this before…so its quite new to me. I have a lot of experimentation to do.
Thanx for that tip!

Reply | Quote

John,

What exactly is ChrW(9632)? Is this simply a character such as Wingdings? If I type the letter “n” and I change the font to Wingdings, it shows a solid rectangle character.
TX

I think I may have answered my Q. However, could I ask if it is accessable from in Excel. If I use the Char function, 9632 does not give me anything. Is there another function to show this set of chars?

Reply | Quote

ChrW returns a Unicode character (see What is Unicode?)

You can use VBA to enter such a character in a cell:

ActiveCell.Value = ChrW(9632)

or

Worksheets(“MySheet”).Range(“D37”).Value = ChrW(9632). As far as I know, there is no equivalent worksheet function.

Reply | Quote

I also thank you, Tony, as I was just now looking to create a status bar. Rudi, if you want a filled box as the progress indicator, try using ChrW(9632) (ChrW gets Unicode characters) instead of Chr(31).

Reply | Quote

Beautiful…this will do great!
Thanx Tony!

Reply | Quote

Hi Rudi

Check Daily Dose of Excel for an example.
It also includes links to other progress bars.

Reply | Quote

I eeeventually got round to trying out Tony and Johns advice on Status Bar Progress bars, and it turned out to be quite superb. Combining the link Tony provided with Johns advice to use ChrW(9632) characters of stead of Chr(31), it looks great!!!
A belated TX guys!

Here is the code…Not that I understand any of it!!

Sub ShowProgress()

Dim i As Long
Dim dPctDone As Double
Dim lSqrNum As Long

Const lMAXSQR As Long = 10

For i = 1 To 30
dPctDone = i / 30
lSqrNum = dPctDone * lMAXSQR
Application.StatusBar = “Project Progress : ” & Application.Rept(ChrW(9632), lSqrNum)
Application.Wait Now + TimeSerial(0, 0, 1)
Next i

Application.StatusBar = False

End Sub

Reply | Quote

Here is a derivation that gives the user an indication of how far the program still has to go.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub ShowProgress()

Dim i As Long
Dim dPctDone As Double
Dim lSqrNum As Long

Const lMAXSQR As Long = 50

For i = 1 To 100
dPctDone = i / 100
lSqrNum = dPctDone * lMAXSQR
Application.StatusBar = “Project Progress : ” _
& Application.Rept(ChrW(9632), lSqrNum) _
& Application.Rept(ChrW(9633), lMAXSQR – lSqrNum)
Application.Wait Now + TimeSerial(0, 0, 1)
Next i

Application.StatusBar = False

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Reply | Quote

Very Nice Don, I wish I had that a few months ago

Reply | Quote

I think it would be wise to caution users that the application should be maximized prior to running this; otherwise the rightmost characters may be hidden leaving the user to suspect an endless loop.

Reply | Quote

Here’s a version that works in both Word and Excel

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Option Explicit

Sub ShowProgress()

Dim i As Long
Dim j As Long
Dim dPctDone As Double
Dim lSqrNum As Long
Dim msg As String

‘this variable needed only for demo
Dim mytimer As Single

Const lMAXSQR As Long = 50

For i = 1 To 100
dPctDone = i / 100
lSqrNum = dPctDone * lMAXSQR
msg = “Project Progress : ”
For j = 1 To lSqrNum
msg = msg & ChrW(9632)
Next j
For j = 1 To lMAXSQR – lSqrNum
msg = msg & ChrW(9633)
Next j

Application.StatusBar = msg
Do While Timer >= mytimer And Timer < mytimer + 1
Loop
mytimer = Timer
Next i

Application.StatusBar = False

End Sub

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Reply | Quote

Hey Don…this is great! TX

Reply | Quote

Make sure you don’t update the bar on every loop, as that slows down your code considerably (up to 100 % slower is no exception!)

Reply | Quote