PDA

View Full Version : Solved: display progress bar



ilyaskazi
05-20-2005, 07:57 PM
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.

Jacob Hilderbrand
05-20-2005, 08:27 PM
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

Bob Phillips
05-21-2005, 01:02 AM
A couple in thepublic domain

Robin Hammand's progreess br class http://www.enhanceddatasystems.com/ED/Pages/ExcelProgressBar.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

ilyaskazi
05-23-2005, 01:27 AM
i hv tried the progressbar of Johnske,

but my manipulation works starts only after progress indications is completed.

i m novice, plz guide..

johnske
05-23-2005, 02:30 AM
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 :)

Bob Phillips
05-23-2005, 02:31 AM
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?

With an English synopsis of what it should do, and the ap design to do it.

Regouin
05-23-2005, 06:39 AM
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.

johnske
05-23-2005, 06:52 AM
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

Regouin
05-23-2005, 07:03 AM
I have hidden the application, so no statusbar :P

johnske
05-23-2005, 07:31 AM
I have hidden the application, so no statusbar :P

See if this works then (in ThisWorkbook module)...Option Explicit
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Application.DisplayStatusBar = True
ActiveWorkbook.Save
Application.DisplayStatusBar = False
End Sub

Regouin
05-23-2005, 07:37 AM
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.

ilyaskazi
05-24-2005, 10:48 PM
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

johnske
05-25-2005, 01:07 AM
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 :)

ilyaskazi
06-18-2005, 02:08 AM
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..


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

johnske
06-18-2005, 02:32 AM
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 :)

ilyaskazi
06-18-2005, 03:03 AM
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.

johnske
06-18-2005, 06:15 AM
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 modulePrivate 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 SubWhat 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: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 SubOf 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 thisSub [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

Regards,
John

sheeeng
06-18-2005, 07:32 AM
Hi all :hi: ,

Actually I used before Progress Bar, without using default built in ProgressBar Control.

Refer to MSDN,

ProgressBar Control Example
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cmctl198/html/vbobjprogressbarx.asp.



My sample below is used with Frame and Label together that display like Progress Bar. :cloud9:

PctDone is the indicator of progress in range 0 - 100%. Just pass parameter form other sub or function.


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


:beerchug:

ilyaskazi
06-20-2005, 02:10 AM
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

johnske
06-20-2005, 02:19 AM
Not a prob... :thumb

ilyaskazi
10-05-2005, 03:43 AM
hey John,

Can v hv progress indication on userform for workbook geting saved in background.

Workbook size is too large to save and takes lot of time.
When dealing with userform, i need to show progress indication exactly as wat microsoft is showing in statusbar.

thanks in advance.