Solved: display progress bar
In my userform, i need to put progress bar (attached here) which i recently downloaded. How do i let know this progress bar to accordingly work as per my requirement.
Means, on pressing start button of my userform, progress bar should start work until the manipulation of data is going on.
Here are a couple of Progress Bar examples.
http://www.vbaexpress.com/kb/getarticle.php?kb_id=411
http://www.vbaexpress.com/kb/getarticle.php?kb_id=169
A couple in thepublic domain
Robin Hammand's progreess br class http://www.enhanceddatasystems.com/E...rogressBar.htm
John Walkenbach's original http://j-walk.com/ss/excel/tips/tip34.htm
and the LED displya version in the status bar http://j-walk.com/ss/excel/files/progressbar.exe
i hv tried the progressbar of Johnske,
but my manipulation works starts only after progress indications is completed.
i m novice, plz guide..
Hi ilyaskazi,
Whichever progress bar you use, you'll have to insert a little bit of code to update the progress indicator somewhere inside the loop you're using. (This place depends on the larger body of your own code).
The only way any of us can help you with this is to see a zipped example of what you're trying to do and determine the best place to insert the code to update the progress.
So can you post your example please?
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
With an English synopsis of what it should do, and the ap design to do it.Originally Posted by johnske
Is it possible to have VBA show a progress bar for saving? Since this is not a loop I cannot simply include the progressbar somewhere, and saving the workbook basically stalls the code till it is done, I now have a userform shown whilst saving to prevent the user from pushing all sorts of buttons and key combinations which might disrupt the process.
Hi Regouin,
If you show your status bar you'll see you already have a built-in progress bar that's activated while saving...
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
I have hidden the application, so no statusbar :P
See if this works then (in ThisWorkbook module)...[vba]Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayStatusBar = True
ActiveWorkbook.Save
Application.DisplayStatusBar = False
End Sub[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
well, i think since i have hidden the application it doesnt show anything now, it is not that important but still a progress bar looks neater then just a screen that tells you to wait, any way that i can show the status bar on a userform, since i basically dont want the user to be able to access Excel in any way. Which is going quite allright as of yet, since the application is hidden once it is loaded and it guides the user through the process just by using userforms.
thankyou john,
i hv understood of using the codes under the loop...
i hv used your progress bar for my application and it now works.
Also i hv made it possible to update accordingly after going through each loops.
thankyou once again.
regards,
ilyaskazi
Hi ilyaskazi,
Not a prob., glad to see you got something working. If you have any further problems with it just post it here...
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
John It is something wrong I did. Progress bar update code is not correct. see below
Below i am calling in my loop to update progressbar accordingly..
Call RunProgress_Bar20 'means 20% completed
Call RunProgress_Bar40 'means 40% completed and so on
now in the actual code i hv change the value of loop For M = 1 To 1 and so on..
see code..
[VBA]
Sub RunProgress_Bar20()
Dim M&, N&
'*******PROPERTIES*******
With SKicker1.ProgressBar1
'(set prog.bar1 properties)
.Height = 18
.Width = 355
.Min = 0
.Max = 100
End With
With SKicker1.ProgressBar2
'(set prog.bar2 properties)
.Height = 15
.Width = 65
.Min = 0
.Max = 1
End With
With SKicker1.Label11
'(set label1 properties)
.Height = 15
.Width = 130
End With
With SKicker1.Label12
'(set label1 properties)
.Height = 15
.Width = 130
End With
'************************
'*******PROGRESS*********
For M = 1 To 1
For N = 1 To 100
SKicker1.ProgressBar1 = N
SKicker1.Label11 = "Individual Progress = " & N & "%"
DoEvents
Next N
SKicker1.ProgressBar2 = M
SKicker1.Label12 = "Over-all Progress = " & M * 20 \ 1 & "%"
DoEvents
Next M
'************************
End Sub
Sub RunProgress_Bar40()
Dim M&, N&
'*******PROPERTIES*******
With SKicker1.ProgressBar1
'(set prog.bar1 properties)
.Height = 18
.Width = 355
.Min = 0
.Max = 100
End With
With SKicker1.ProgressBar2
'(set prog.bar2 properties)
.Height = 15
.Width = 130
.Min = 0
.Max = 2
End With
With SKicker1.Label11
'(set label1 properties)
.Height = 15
.Width = 130
End With
With SKicker1.Label12
'(set label1 properties)
.Height = 15
.Width = 130
End With
'************************
'*******PROGRESS*********
For M = 1 To 2
For N = 1 To 100
SKicker1.ProgressBar1 = N
SKicker1.Label11 = "Individual Progress = " & N & "%"
DoEvents
Next N
SKicker1.ProgressBar2 = M
SKicker1.Label12 = "Over-all Progress = " & M * 20 \ 1 & "%"
DoEvents
Next M
'************************
End Sub
Sub RunProgress_Bar60()
Dim M&, N&
'*******PROPERTIES*******
With SKicker1.ProgressBar1
'(set prog.bar1 properties)
.Height = 18
.Width = 355
.Min = 0
.Max = 100
End With
With SKicker1.ProgressBar2
'(set prog.bar2 properties)
.Height = 15
.Width = 260
.Min = 0
.Max = 3
End With
With SKicker1.Label11
'(set label1 properties)
.Height = 15
.Width = 130
End With
With SKicker1.Label12
'(set label1 properties)
.Height = 15
.Width = 130
End With
'************************
'*******PROGRESS*********
For M = 1 To 3
For N = 1 To 100
SKicker1.ProgressBar1 = N
SKicker1.Label11 = "Individual Progress = " & N & "%"
DoEvents
Next N
SKicker1.ProgressBar2 = M
SKicker1.Label12 = "Over-all Progress = " & M * 20 \ 1 & "%"
DoEvents
Next M
'************************
End Sub
Sub RunProgress_Bar80()
Dim M&, N&
'*******PROPERTIES*******
With SKicker1.ProgressBar1
'(set prog.bar1 properties)
.Height = 18
.Width = 355
.Min = 0
.Max = 100
End With
With SKicker1.ProgressBar2
'(set prog.bar2 properties)
.Height = 15
.Width = 300
.Min = 0
.Max = 4
End With
With SKicker1.Label11
'(set label1 properties)
.Height = 15
.Width = 130
End With
With SKicker1.Label12
'(set label1 properties)
.Height = 15
.Width = 130
End With
'************************
'*******PROGRESS*********
For M = 1 To 4
For N = 1 To 100
SKicker1.ProgressBar1 = N
SKicker1.Label11 = "Individual Progress = " & N & "%"
DoEvents
Next N
SKicker1.ProgressBar2 = M
SKicker1.Label12 = "Over-all Progress = " & M * 20 \ 1 & "%"
DoEvents
Next M
'************************
End Sub
Sub RunProgress_Bar100()
Dim M&, N&
'*******PROPERTIES*******
With SKicker1.ProgressBar1
'(set prog.bar1 properties)
.Height = 18
.Width = 355
.Min = 0
.Max = 100
End With
With SKicker1.ProgressBar2
'(set prog.bar2 properties)
.Height = 15
.Width = 352
.Min = 0
.Max = 5
End With
With SKicker1.Label11
'(set label1 properties)
.Height = 15
.Width = 130
End With
With SKicker1.Label12
'(set label1 properties)
.Height = 15
.Width = 130
End With
'************************
'*******PROGRESS*********
For M = 1 To 5
For N = 1 To 100
SKicker1.ProgressBar1 = N
SKicker1.Label11 = "Individual Progress = " & N & "%"
DoEvents
Next N
SKicker1.ProgressBar2 = M
SKicker1.Label12 = "Over-all Progress = " & M * 20 \ 1 & "%"
DoEvents
Next M
'************************
End Sub
[/VBA]
Hi ilyaskazi,
This seems very lengthy, can you strip any data that you don't want made public from a copy of the workbook, zip it and post it here (use 'manage attachments') for us to look at pls?
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Below i am calling in my loop to update progressbar accordingly..
Call RunProgress_Bar20 'means 20% completed
Call RunProgress_Bar40 'means 40% completed and so on
now in the actual code i hv change the value of loop For M = 1 To 1 and so on..
I need to run this progressbar very smooth and more fast.
If Over-all progress is 20% then next must start from 20% and so on.
In my code it re-starts from 0.
Hi ilyaskazi,
I've had a quick look... Now, firstly, the properties only have to be set once when the user form is activated, so this bit of code goes into the user form module[vba]Private Sub UserForm_Activate()
Dim M&, N&
'*******PROPERTIES*******
With UserForm1.ProgressBar1
'(set prog.bar1 properties)
.Height = 18
.Width = 355
.Min = 0
.Max = 100
End With
With UserForm1.ProgressBar2
'(set prog.bar2 properties)
.Height = 15
.Width = 65
.Min = 0
.Max = 1
End With
With UserForm1.Label11
'(set label1 properties)
.Height = 15
.Width = 130
End With
With UserForm1.Label12
'(set label1 properties)
.Height = 15
.Width = 130
End With
'************************
End Sub[/vba]What I don't understand is why you have separate procedures for each 20% of the code, it's more usual to run this as one continuous block of code as I've shown in the kb entry.
Before we get to that though, I was hoping to see what you wanted the code to be run for... For example, the idea of the twin progress indicators is for cases where you have two things happening (say) a lot of workbooks open that have to be i) processed and then ii) saved and closed. The over-all progress would then indicate how many workbooks had been saved and closed {ii)} while the individual progress would indicate the state of the processing of the current workbook {i)}
However, if there are not two separate things going on, there is no need for two progress indicators - only the one.
Now, let us say that you only have one sheet that requires processing and this processing doesn't involve a loop, you would then have something like this in a standard module:[vba]Sub DoingSomething()
'declaring my variables here
With UserForm1
.Show
.ProgressBar1 = 1
End With
DoEvents
'code to do some things here
UserForm1.ProgressBar1 = 10
DoEvents
'more code to do some other things here
UserForm1.ProgressBar1 = 20
DoEvents
'more code here
UserForm1.ProgressBar1 = 30
DoEvents
'more code
UserForm1.ProgressBar1 = 40
DoEvents
' "
' "
' "
' "
'and so on till it's 100% done
End Sub[/vba]Of course, it's more usual to have a loop of some sort to do all this, so your standard module would contain code something like this[vba]Sub [VBA] DoingSomething()
Dim N As Long
'declare all your other variables
UserForm1.Show
For N = 1 To 100
UserForm1.ProgressBar1 = N
DoEvents
'your processing code here
Next N
End Sub
[/VBA]
Regards,
John
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Hi all,
Actually I used before Progress Bar, without using default built in ProgressBar Control.
Refer to MSDN,
ProgressBar Control Example
http://msdn.microsoft.com/library/de...ogressbarx.asp.
My sample below is used with Frame and Label together that display like Progress Bar.
PctDone is the indicator of progress in range 0 - 100%. Just pass parameter form other sub or function.
[VBA]
Sub UpdateProgressBar(PctDone As Double)
With UserForm1
' Update the Caption property of the Frame control.
.FrameProgress.Caption = "Progress : " & Format(PctDone, "0%")
' Widen the Label control.
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 25)
End With
' The DoEvents allows the UserForm to update.
DoEvents
End Sub
[/VBA]
thankyou onceagain John for explaining this in depth.
Without this details, it would have not possible for me. You made it very easy to understand.
Earlier I was having a very wrong idea of showing progress bar. Now its done and working smooth, fast and perfect.
regards,
ilyas kazi
Not a prob...
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Posting Permissions
Reply With Quote
