DownLoad Bar

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Snoopy - 02 Jul 2007 09:03 GMT

Hey fellows
To fresh up my update-macro I want to show the user a DownLoad Bar
that increase (fill in with blue color in message box or similar)
while the macro runs.
I understand that I place the instructions on several check-points in
the macro-system such as those (the bars) appears on the screen as
increasing. But I dont know how to do it.

Could anybody help me?

Regards
Snoopy

Reply to this Message

Dave Peterson - 02 Jul 2007 12:08 GMT

You may want to look at John Walkenbach's site:
http://j-walk.com/ss/excel/tips/tip34.htm

> Hey fellows
> To fresh up my update-macro I want to show the user a DownLoad Bar
[quoted text clipped - 8 lines]
> Regards
> Snoopy

Signature

Dave Peterson

Reply to this Message

Snoopy - 03 Jul 2007 13:16 GMT

> You may want to look at John Walkenbach's site:http://j-walk.com/ss/excel/tips/tip34.htm
>
[quoted text clipped - 16 lines]
>
> Dave Peterson

Thanks
I'm still struggling.
My macro-stucture is build up by several macros that each spend som
time.
It would be nice to display a progressbar that indicates the progress.
I've created a subform1 as described on John Walkenbach's website, but
I dont see how to create loops or subrutines to create this expanding
progressbar.

Regards
Snoopy

Reply to this Message

Dave Peterson - 03 Jul 2007 13:24 GMT

Maybe you could have a public variable that keeps track of the "step" that
you're in and have each routine increment that variable, then pass the variable
to the routine that displays the statusbar.

If you want a simpler approach, you could add a message to the statusbar:

application.statusbar = "Now processing whateveryouwant here"

And finish with:
application.statusbar = false

> > You may want to look at John Walkenbach's site:http://j-walk.com/ss/excel/tips/tip34.htm
> >
[quoted text clipped - 28 lines]
> Regards
> Snoopy

Signature

Dave Peterson

Reply to this Message

Snoopy - 03 Jul 2007 14:55 GMT

> Maybe you could have a public variable that keeps track of the "step" that
> you're in and have each routine increment that variable, then pass the variable
[quoted text clipped - 47 lines]
>
> - Vis sitert tekst -

Thanks Dave
I've "sort of" solved the problem by using application.statusbar
message as you suggested.
However it would be nice to no ho to make this routine that displays a
progress bar.
I have made som sub-routines as this
Sub B_TI()
Application.ScreenUpdating = True
With UserForm2
UserForm2.LabelProgress.Width = 20
.FrameProgress.Caption = ("10 %")
UserForm2.Show
End With
DoEvents
' Unload UserForm2
Application.ScreenUpdating = False
End Sub
- to place in certain positions in the macro-system, bur I have to
close them manually to make the macro structure go on

I'm very happy for your help so far
Regards Snoopy

Reply to this Message

Dave Peterson - 03 Jul 2007 16:08 GMT

John's code in the Main procedure could call all the other routines that you
need.

Kind of:

Option Explicit
Sub Test()
' The UserForm1_Activate sub calls Main
UserForm1.LabelProgress.Width = 0
UserForm1.Show
End Sub

Sub Main()
' Inserts random numbers on the active worksheet
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single

If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
Cells.Clear
Application.ScreenUpdating = False
PctDone = 0.1
Call UpdatePB(PctDone)
'do some more stuff
Application.Wait Now + TimeSerial(0, 0, 3)

PctDone = 0.2
Call UpdatePB(PctDone)
'do some more stuff, I'm just gonna use wait to simulate more stuff
Application.Wait Now + TimeSerial(0, 0, 3)

PctDone = 0.9
Call UpdatePB(PctDone)
'do some more stuff
Application.Wait Now + TimeSerial(0, 0, 3)

PctDone = 1#
Call UpdatePB(PctDone)
'do some more stuff
Application.Wait Now + TimeSerial(0, 0, 2)

Unload UserForm1
End Sub

Sub UpdatePB(PctDone)
With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
DoEvents
End Sub

> > Maybe you could have a public variable that keeps track of the "step" that
> > you're in and have each routine increment that variable, then pass the variable
[quoted text clipped - 70 lines]
> I'm very happy for your help so far
> Regards Snoopy

Signature

Dave Peterson

Reply to this Message