Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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.?
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    Hi Rudi

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

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    Beautiful...this will do great!
    Thanx Tony!
    Regards,
    Rudi

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 5 Times in 5 Posts

    Re: Show a progress bar on the status bar? (Excel

    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).
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    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! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Regards,
    Rudi

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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!! <img src=/S/drop.gif border=0 alt=drop width=23 height=23>

    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
    Attached Images Attached Images
    Regards,
    Rudi

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    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?
    Regards,
    Rudi

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Show a progress bar on the status bar? (Excel

    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.

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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


    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Regards
    Don

  10. #10
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    Very Nice Don, I wish I had that a few months ago <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Jerry

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    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.
    Regards
    Don

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Show a progress bar on the status bar? (Excel

    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

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Regards
    Don

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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!)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  14. #14
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Hey Don...this is great! TX
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •