VBA Progress Bar (?)

[Pinaceous]

Hi All,

I'm working with Excel 2013 and I am using a button click to 'Call' upon several macros that filter out a web query and then to display it on 'Sheet1'.

My (?) is then, how do I tie a 'Progress Bar' where it can 'Counter' until the end of the running macros is completed?

Any help, even if you don't have a specific answer is fine.

Many thanks in advance.

R/
Paul

 

[crewaustin66]

The problem is that in order to use a progress bar you have to have something to count. Since you don't know how long it will take you are better off using a animated "spinner" graphic or a time counter just to give the user something to look at.

 

[Pinaceous]

Hi Crew,

How would I insert an animated 'spinner' graphic or a 'time counter'?

Would you have a code for this?

Thanks,
-Pin

 

[Pinaceous]

Hi Crew,

Could I use the running length of the 'macro' to count as the counter?

-Pin

 

[crewaustin66]

Do you know the run length of the macro when you first kick it off? If you knew that, and it was consistent, then yes you could make a progress bar.

 

[shg]

I am using a button click to 'Call' upon several macros that filter out a web query and then to display it on 'Sheet1'.

You could set Application.Cursor = xlWait, could write to the status bar as each macro completes,

Macro 1 of 5
Macro 2 of 5
Macro 3 of 5
...

... and then set Application.Cursor back to xlDefault

 

[COwen]

I realize that this thread is 5 years old but for the sake of people looking back on it...

You can animate label controls using .left and .top commands. for example:

Private sub Commandbutton1_click()
label1.left = 1
doevents
sleep 100
label1.left = 3
doevents
sleep 100
label1.left = 5
doevents
sleep 100
label1.left = 7
End Sub

As a note, you will need to declare the Sleep function. The code for that is as follows for 64 bit systems:

Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

I do not know for sure the code for 32 bit systems but I think it is:

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

You can get very complex if you like as in the following code:

Private Sub UserForm_Activate()
    
    Me.Label1.Caption = "Authenticating..."
    DoEvents
    Sleep 550
    Me.Label1.Caption = "Welcome " & Sheet1.Range("A1").Value & ".  Please wait while I retrieve the program."
    
    SQR2.Top = 60
    SQR1.Top = 49
    DoEvents
    Sleep 18
    SQR2.Top = 62
    SQR1.Top = 51
    DoEvents
    Sleep 18
    SQR2.Top = 64
    SQR1.Top = 53
    DoEvents
    Sleep 18
    SQR2.Top = 66.5
    SQR1.Top = 55.5
    DoEvents
    Sleep 18
    SQR1.Top = 69
    SQR2.Top = 58
    DoEvents
    Sleep 400
    
    Me.Label2.Caption = "Retrieving Data..."
    SQR1.Left = 86
    DoEvents
    Sleep 18
    SQR1.Left = 88
    DoEvents
    Sleep 18
    SQR1.Left = 90
    DoEvents
    Sleep 18
    SQR1.Left = 92.5
    DoEvents
    Sleep 18
    SQR1.Left = 95
    DoEvents
    Sleep 400
    
    SQR3.Top = 55.5
    SQR1.Top = 66.5
    DoEvents
    Sleep 18
    SQR3.Top = 53
    SQR1.Top = 64
    DoEvents
    Sleep 18
    SQR3.Top = 51
    SQR1.Top = 62
    DoEvents
    Sleep 18
    SQR3.Top = 49
    SQR1.Top = 60
    DoEvents
    Sleep 18
    SQR1.Top = 47
    SQR3.Top = 58
    DoEvents
    Sleep 400
    
    Me.Label2.Caption = "Updating..."
    SQR1.Left = 97
    DoEvents
    Sleep 18
    SQR1.Left = 99
    DoEvents
    Sleep 18
    SQR1.Left = 101
    DoEvents
    Sleep 18
    SQR1.Left = 103.5
    DoEvents
    Sleep 18
    SQR1.Left = 106
    DoEvents
    Sleep 400
    
    SQR4.Top = 60
    SQR1.Top = 49
    DoEvents
    Sleep 18
    SQR4.Top = 62
    SQR1.Top = 51
    DoEvents
    Sleep 18
    SQR4.Top = 64
    SQR1.Top = 53
    DoEvents
    Sleep 18
    SQR4.Top = 66.5
    SQR1.Top = 55.5
    DoEvents
    Sleep 18
    SQR1.Top = 69
    SQR4.Top = 58
    DoEvents
    Sleep 400

    SQR1.Left = 108
    DoEvents
    Sleep 18
    SQR1.Left = 110
    DoEvents
    Sleep 18
    SQR1.Left = 112
    DoEvents
    Sleep 18
    SQR1.Left = 114.5
    DoEvents
    Sleep 18
    SQR1.Left = 117
    DoEvents
    Sleep 400
    
    Me.Label2.Caption = "Restoring Settings..."
    SQR5.Top = 55.5
    SQR1.Top = 66.5
    DoEvents
    Sleep 18
    SQR5.Top = 53
    SQR1.Top = 64
    DoEvents
    Sleep 18
    SQR5.Top = 51
    SQR1.Top = 62
    DoEvents
    Sleep 18
    SQR5.Top = 49
    SQR1.Top = 60
    DoEvents
    Sleep 18
    SQR1.Top = 47
    SQR5.Top = 58
    DoEvents
    Sleep 400
    
    SQR1.Left = 114.5
    DoEvents
    Sleep 18
    SQR1.Left = 112
    DoEvents
    Sleep 18
    SQR1.Left = 110
    DoEvents
    Sleep 18
    SQR1.Left = 108
    DoEvents
    Sleep 18
    SQR1.Left = 106
    DoEvents
    Sleep 400

    SQR4.Top = 60
    SQR1.Top = 49
    DoEvents
    Sleep 18
    SQR4.Top = 62
    SQR1.Top = 51
    DoEvents
    Sleep 18
    SQR4.Top = 64
    SQR1.Top = 53
    DoEvents
    Sleep 18
    SQR4.Top = 66.5
    SQR1.Top = 55.5
    DoEvents
    Sleep 18
    SQR1.Top = 69
    SQR4.Top = 58
    DoEvents
    Sleep 400
    Label3.Caption = "This part will authenticate you. and then open the program."
    
    SQR1.Left = 103.5
    DoEvents
    Sleep 18
    SQR1.Left = 101
    DoEvents
    Sleep 18
    SQR1.Left = 99
    DoEvents
    Sleep 18
    SQR1.Left = 97
    DoEvents
    Sleep 18
    SQR1.Left = 95
    DoEvents
    Sleep 400
    
    SQR3.Top = 55.5
    SQR1.Top = 66.5
    DoEvents
    Sleep 18
    SQR3.Top = 53
    SQR1.Top = 64
    DoEvents
    Sleep 18
    SQR3.Top = 51
    SQR1.Top = 62
    DoEvents
    Sleep 18
    SQR3.Top = 49
    SQR1.Top = 60
    DoEvents
    Sleep 18
    SQR1.Top = 47
    SQR3.Top = 58
    DoEvents
    Sleep 400
    
    Me.Label1.Caption = ""
    SQR1.Left = 92.5
    DoEvents
    Sleep 18
    SQR1.Left = 90
    DoEvents
    Sleep 18
    SQR1.Left = 88
    DoEvents
    Sleep 18
    SQR1.Left = 86
    DoEvents
    Sleep 18
    SQR1.Left = 84.5
    DoEvents
    Sleep 400
    '++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

    
    SQR1.Visible = False
        SQR2Visible = False
            SQR3.Visible = False
                SQR4.Visible = False
                    SQR5.Visible = False
                    
    
    DoEvents
    Sleep 300

Unload Me
frmMenu.Show
End Sub

 

[COwen]

This "Shuffles" four squares around (See this video) to do this, simply create 5 label controls. Name them SQR1-SQR5. You will also need 3 normal labels named Label1-Label3