Macro and slowing a progress bar.

  • Thread starter Thread starter Rockee052
  • Start date Start date
R

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
 
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)
 
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
 
Rockee052 said:
*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 *
Click to expand...

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
 
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
 
Back
Top