Macro and slowing a progress bar.

[Rockee052]

Hello World,

I have a marco that clears some cell contents and does a few othe
things. I also made a progress bar to show the progress of the marco
The issue I'm having is that the macro works WAY to fast. My progres
bar shows for a half of a second. And since I spent a half day makin
the progress bar (newbie), I would like to enjoy watching it for a
leat a few seconds. So, can anyone help me slow down this macro?
looked up the OnTime method and I not sure if it would apply here.

Here is my code:


Sub ClearSheet()

' Inputing "0" in selected ranges
Range("C13,M13,O15,Q13,Q15,C32,M32,O34,Q32," & _
"Q34,T33,C51,M51,O53,Q51,Q53,S61:T63").FormulaR1C1 = "0"

' Requesting an update in percentage
Call UpdateProgress(0.1)
Call UpdateProgress(0.8)

' Unprotecting the sheet
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect Password:="1234"
End If

' Clearing selected ranges
Range("B6:H10,L6:R10,C12,J12,J14,J17:J18,M12,T12," & _
"B21:H29,L21:R29,C31,J31,J33,J36:J37," & _
"M31,T31,B40:H48,L40:R48,C50,J50,J52," & _
"J55:J56,M50,T50,F60,Q2:S2,B6").ClearContents

' Requesting final update "90%"
Call UpdateProgress(0.9)

Range("B6").Select
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect Password:="1234"
End If

PctDone = ClearContents
Call UpdateProgress(PctDone)
Unload UserForm1
End Sub

Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = 24 * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub


Thanks,

Rockee Freema

 

[KJTFS]

put a bunch of large loops with the code doevents in them, that is abou
the simplist way to slow down so you can see your bar.

Keith
www.kjtfs.co

 

[Bob Phillips]

Surely, if the macro is too fast for the progress bar, you don't need a
progress bar!

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Rockee052]

I know that my progress bar was not needed, i just wanted to see how on
works in action. What I used to slow it down was:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Work fin

 

[KJTFS]

*I know that my progress bar was not needed, i just wanted to see how
one works in action. What I used to slow it down was:

newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime

Work fine *

Sounds like a good method, BTW even if something isn't needed don't
stop from developing it, especially if you have the time to develope
it. I use VBA all day at work but I still found a need to start my
consulting and applications company to keep me learning and doing new
things. You never want your computer skills to get stale.

Keith
www.kjtfs.com

 

[Rockee052]

KJTFS,

Thanks for the comment...

I started messing around with VBA about two months ago and have foun
it very very interesting and complicated. My goal is to find a onlin
class somewhere and study study study. In the meantime I will keep o
playing around.

Again, thanks for the comment

Rockee Freema