Macro and slowing a progress bar.
 

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").Formula R1C1 = "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

--
Message posted from http://www.ExcelForum.com

Macro and slowing a progress bar.
 

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

--
Message posted from http://www.ExcelForum.com

Macro and slowing a progress bar.
 

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 " wrote in message
...
Hello World,

I have a marco that clears some cell contents and does a few other
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 progress
bar shows for a half of a second. And since I spent a half day making
the progress bar (newbie), I would like to enjoy watching it for at
leat a few seconds. So, can anyone help me slow down this macro? I
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").Formula R1C1 = "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 Freeman


---
Message posted from http://www.ExcelForum.com/

Macro and slowing a progress bar.
 

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

--
Message posted from http://www.ExcelForum.com