Progress bar with no loop ?

[Sahak]

Hi All,

Does progress bars with Excel VBA works only if there is a Loop?
Is it possible to have progress bar while more than one macros running, without looping at all?

Thank you in advance.

 

[shg]

How would the progress bar get updated without a loop?

 

[Sahak]

Hi SHG,

Thank you for your reply. You are right, it is impossible.

It could be something like this, let say we have 3 macros, first macro calls second macro & second macro calls third macro, & let say it takes 15 seconds. At the beginning of first macro will be code something like this: ProgressBar.start & at the end of 3rd macro: ProgressBar.stop

 

[shg]

You could do that.

 

[PritishS]

Hi All,

Does progress bars with Excel VBA works only if there is a Loop?
Is it possible to have progress bar while more than one macros running, without looping at all?

Thank you in advance.

Hi,
I know I'm late to this post. It is possible to show a Progress Bar on UserForm without LOOP.
My answer may help someone to new who comes to this discussion.

Make a Userform
Step1:
Add a userform named 'frmProgressForm'. Width: 285, Height: 80, Zoom: 100, Caption: Progress Bar...
Step2:
Add a Frame on useform named 'FrameProgress'. Width: 265, Height: 65, Zoom: 100, Caption: 0%
Step3:
Add a lable on Frame named 'lblProgress'. Width: 250 (this should not be changed. Progress bar is % will increase based on this 250), Height: 18, Zoom: 100, Caption: Blank

Add below Code inside 'frmProgressForm'

Private Sub UserForm_Activate() 
     Call progessbar_blank
End Sub

Add a First Sub in a Module

Sub progessbar_blank()
' leave this macro blank.
End Sub

Add a Second All Subs Progress_bar1 to Progress_bar11 in same Module

Sub Progress_bar1()
'Bar1
frmProgressForm.FrameProgress.Caption = "5% Completed"
frmProgressForm.lblProgress.Width = 12.5
frmProgressForm.Show vbModeless
frmProgressForm.Repaint
DoEvents
End Sub

Sub Progress_bar2()
'Bar2
frmProgressForm.FrameProgress.Caption = "10% Completed"
frmProgressForm.lblProgress.Width = 25
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar3()
'Bar3
frmProgressForm.FrameProgress.Caption = "20% Completed"
frmProgressForm.lblProgress.Width = 50
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar4()
'Bar4
frmProgressForm.FrameProgress.Caption = "30% Completed"
frmProgressForm.lblProgress.Width = 75
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar5()
'Bar5
frmProgressForm.FrameProgress.Caption = "40% Completed"
frmProgressForm.lblProgress.Width = 100
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar6()
'Bar6
frmProgressForm.FrameProgress.Caption = "50% Completed"
frmProgressForm.lblProgress.Width = 125
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar7()
'Bar7
frmProgressForm.FrameProgress.Caption = "60% Completed"
frmProgressForm.lblProgress.Width = 150
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar8()
'Bar8
frmProgressForm.FrameProgress.Caption = "70% Completed"
frmProgressForm.lblProgress.Width = 175
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar9()
'Bar9
frmProgressForm.FrameProgress.Caption = "80% Completed"
frmProgressForm.lblProgress.Width = 200
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar10()
'Bar10
frmProgressForm.FrameProgress.Caption = "90% Completed"
frmProgressForm.lblProgress.Width = 225
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
End Sub

Sub Progress_bar11()
'Bar11
frmProgressForm.FrameProgress.Caption = "100% Completed"
frmProgressForm.lblProgress.Width = 250
Application.Wait (Now + TimeValue("0:00:01"))
DoEvents
frmProgressForm.Hide
End Sub

Now Call Progress_bar1 to Call Progress_bar11 throughout any of your code. No Looping is required.
Example Code.

Sub AnyCode()
'It's your decision where to call the Subs. But use all 11 subs
'from Progress_bar1 to Progress_bar11
'250 width of lblProgress distributed through Sub Progress_bar1 to Sub Progress_bar11'

Call Progress_bar1
'Some part of your code...

Call Progress_bar2
'Some part of your code...

Call Progress_bar3
'Some part of your code...

Call Progress_bar4
'Some part of your code...

Call Progress_bar5
'Some part of your code...

Call Progress_bar6
'Some part of your code...

Call Progress_bar7
'Some part of your code...

Call Progress_bar8
'Some part of your code...

Call Progress_bar9
'Some part of your code...

Call Progress_bar10
'Some part of your code...

Call Progress_bar11
'Some part of your code...

End Sub

Hope this help someone in need.

Thank you
PritishS

 

[RoryA]

There's really no need for 11 separate update routines. You should just pass an argument indicating the current percentage complete and use one routine.

 

[PritishS]

There's really no need for 11 separate update routines. You should just pass an argument indicating the current percentage complete and use one routine.

Thank you pointing this Sir.
Can you give some exapmle to my code, so that it will be short and simple?

 

[RoryA]

Typically you'd have one routine that just shows the form initially, then your update routine would be in the form, and look something like:

Public Sub UpdateProgress(PercentDone as Double)
With Me
   .FrameProgress.Caption = PercentDone & "% Completed"
   .lblProgress.Width = 250 * PercentDone  / 100
   .Repaint
End With
DoEvents
End Sub

then the calling code would just use code like:

frmProgress.UpdateProgress 5

 

[PritishS]

Hi,
Where shall this code will go? I have added this in userform Code. Shall I add this code in a module?

Public Sub UpdateProgress(PercentDone as Double)

Also where shall I use this code? I have a long code. I have added at the beginig of the code. but nothing happens.

frmProgressForm.UpdateProgress 5

Sub MyCode()
Dim.....
Dim....

frmProgressForm.UpdateProgress 5
--my codes goes here..

End Sub

Can you please suggest. Thanks

 

[Sahak]

Hi guys,

Thank you for your reply to my post. I have tried to run the macros above & did not get it to work, could you please attach an example file?
Thank you in advance.