Progress bar and Status bar.

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,961
Office Version
  1. 2019
Platform
  1. Windows
Hello all,

How can I use the status bar at the bottom to be used as a progress bar for notifying the end-user to wait until vba completes running in the background. So far I have experimented a little with unicode character ChrW(9616) and liked the results. With my novice vba know how I can't make it to progress though. Thus will appreciate a lot any feedback on this. The following is the code I use

VBA Code:
Sub progbar()
Dim Lst
I = ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616) & ChrW(9616)
Application.StatusBar = Lst
End Sub

After running the code I get the same as the image...

1606470467183.png


So how do I change the code to make this into an animated progress bar. I believe the status bar has room for 100 character and the current characters count is somewhere around 30. What if I want to progress it by 5 characters every N seconds til I reach 100th character and the end of my code at the same time?? I will have to make an estimate. So if my code takes 40 seconds to run the N will need to be 2.5 seconds .

Anyone please want to give it a try?
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,117
Office Version
  1. 2016
Platform
  1. Windows
Something like this maybe:

VBA Code:
Sub progbar()

    Dim Lst As String, i As Long
   
    Application.StatusBar = False
        For i = 1 To 20
            Lst = String(i * 5, ChrW(9616))
            Application.StatusBar = CStr(Lst)
            Delay 2.5
        Next i
    Application.StatusBar = False

End Sub

Private Sub Delay(ByVal Secs As Long)

    Dim sngTimer As Single
   
    sngTimer = Timer
    Do
        DoEvents
    Loop Until Timer - sngTimer >= Secs

End Sub
 

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,961
Office Version
  1. 2019
Platform
  1. Windows
Something like this maybe:

VBA Code:
Sub progbar()

    Dim Lst As String, i As Long
 
    Application.StatusBar = False
        For i = 1 To 20
            Lst = String(i * 5, ChrW(9616))
            Application.StatusBar = CStr(Lst)
            Delay 2.5
        Next i
    Application.StatusBar = False

End Sub

Private Sub Delay(ByVal Secs As Long)

    Dim sngTimer As Single
 
    sngTimer = Timer
    Do
        DoEvents
    Loop Until Timer - sngTimer >= Secs

End Sub

Thank you Jaafar.
This works nicely. Really appreciate you giving your time and expertise.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,117
Office Version
  1. 2016
Platform
  1. Windows
@omairhe

Glad I could help.

You could pass the arguments to a generic routine for more flexibility as follows:

VBA Code:
Sub RunProgressBar(ByVal DispCharacter As String, ByVal Step As Integer, ByVal MaxLength As Integer, ByVal Delay As Single)

    Dim i As Integer, sngTimer As Single
   
    If Step > MaxLength Then GoTo errHandler
    Step = Abs(Step)

    Application.StatusBar = False
        For i = 1 To MaxLength \ Step
            Application.StatusBar = String(i * Step, DispCharacter)
            sngTimer = Timer
            Do
                DoEvents
            Loop Until Timer - sngTimer >= Abs(Delay)
        Next i
    Application.StatusBar = False
   
    Exit Sub
   
errHandler:
   
    MsgBox "Step must be <= than MaxLength ", , "Error"

End Sub

An then you call it like this :
VBA Code:
Sub progbar()

    RunProgressBar ChrW(9616), 5, 100, 2.5

End Sub
 
Last edited:
Solution

omairhe

Well-known Member
Joined
Mar 26, 2009
Messages
1,961
Office Version
  1. 2019
Platform
  1. Windows
@omairhe

Glad I could help.

You could pass the arguments to a generic routine for more flexibility as follows:

VBA Code:
Sub RunProgressBar(ByVal DispCharacter As String, ByVal Step As Integer, ByVal MaxLength As Integer, ByVal Delay As Single)

    Dim i As Integer, sngTimer As Single
  
    If Step > MaxLength Then GoTo errHandler
    Step = Abs(Step)

    Application.StatusBar = False
        For i = 1 To MaxLength \ Step
            Application.StatusBar = String(i * Step, DispCharacter)
            sngTimer = Timer
            Do
                DoEvents
            Loop Until Timer - sngTimer >= Abs(Delay)
        Next i
    Application.StatusBar = False
  
    Exit Sub
  
errHandler:
  
    MsgBox "Step must be <= than MaxLength ", , "Error"

End Sub

An then you call it like this :
VBA Code:
Sub progbar()

    RunProgressBar ChrW(9616), 5, 100, 2.5

End Sub
I try your code with running my code but it seems that Excel can't run both the codes at the same time. First it will run yours and after completing the script it runs the next script.
Is there a way around this or perhaps somewhere I missed the point?
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
8,117
Office Version
  1. 2016
Platform
  1. Windows
If you run the code in post#4 then don't run the code in post#2... Either you use the code in post#2 or the code in post#4 ... You can't run both codes at the same time.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,846
Messages
5,598,425
Members
414,237
Latest member
tereres

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top