Hi.
Just a few comments before I attempt your problem.
Range("B6:H10").Select
Selection.ClearContents
Can be simplified to Range("B6:H10").ClearContents
If you don't want your users to see the window change position try removing
ActiveWindow.LargeScroll Down:=-1
I nicer way might be to make all those selections just one giant range.
eg.
Range("C13,M13,O15,Q13,Q15,C32,M32,O34,Q32," & _
"Q34,T33,C51,M51,O53,Q51,Q53,S61:T63").Formula R1C1 = "0"
Range("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
To answer your question, you need to call UpdateProgress each time you want
the percent to change.
So throughout your code you'll place, for example, Call UpdateProgress(0.5)
which should draw the progress at 50%
"Rockee052" wrote in message
...
Hi,
I have made a progress bar based on a macro, the macro just clears the
contents of some selected ranges. The problem I have is the the
progress bar does not show the percent complete
until its finished clearing the contents. Can anyone help? Here is what
I'm working with...
Range("B6:H10").Select
Selection.ClearContents
Range("L6:R10").Select
Selection.ClearContents
Range("C12").Select
Selection.ClearContents
Range("C13").Select
ActiveCell.FormulaR1C1 = "0"
Range("J12").Select
Selection.ClearContents
Range("J14").Select
Selection.ClearContents
Range("J17").Select
Selection.ClearContents
Range("J18").Select
Selection.ClearContents
Range("M12").Select
Selection.ClearContents
Range("M13").Select
ActiveCell.FormulaR1C1 = "0"
Range("O15").Select
ActiveCell.FormulaR1C1 = "0"
Range("Q13").Select
ActiveCell.FormulaR1C1 = "0"
Range("Q15").Select
ActiveCell.FormulaR1C1 = "0"
Range("T12").Select
Selection.ClearContents
Range("B21:H29").Select
Selection.ClearContents
Range("L21:R29").Select
Selection.ClearContents
Range("C31").Select
Selection.ClearContents
Range("C32").Select
ActiveCell.FormulaR1C1 = "0"
Range("J31").Select
Selection.ClearContents
Range("J33").Select
Selection.ClearContents
Range("J36").Select
Selection.ClearContents
Range("J37").Select
Selection.ClearContents
Range("M31").Select
Selection.ClearContents
Range("M32").Select
ActiveCell.FormulaR1C1 = "0"
Range("O34").Select
ActiveCell.FormulaR1C1 = "0"
Range("Q32").Select
ActiveCell.FormulaR1C1 = "0"
Range("Q34").Select
ActiveCell.FormulaR1C1 = "0"
Range("T31").Select
Selection.ClearContents
Range("T33").Select
ActiveCell.FormulaR1C1 = "0"
Range("B40:H48").Select
Selection.ClearContents
Range("L40:R48").Select
Selection.ClearContents
Range("C50").Select
Selection.ClearContents
Range("C51").Select
ActiveCell.FormulaR1C1 = "0"
Range("J50").Select
Selection.ClearContents
Selection.ClearContents
Range("J52").Select
Selection.ClearContents
Range("J55").Select
Selection.ClearContents
Range("J56").Select
Selection.ClearContents
Range("M50").Select
Selection.ClearContents
Range("M51").Select
ActiveCell.FormulaR1C1 = "0"
Range("O53").Select
ActiveCell.FormulaR1C1 = "0"
Range("Q51").Select
ActiveCell.FormulaR1C1 = "0"
Range("Q53").Select
ActiveCell.FormulaR1C1 = "0"
Range("T50").Select
Selection.ClearContents
Range("F60").Select
Selection.ClearContents
Range("S61").Select
ActiveCell.FormulaR1C1 = "0"
Range("T61").Select
ActiveCell.FormulaR1C1 = "0"
Range("T62").Select
ActiveCell.FormulaR1C1 = "0"
Range("S62").Select
ActiveCell.FormulaR1C1 = "0"
Range("S63").Select
ActiveCell.FormulaR1C1 = "0"
Range("T63").Select
ActiveCell.FormulaR1C1 = "0"
Range("T64").Select
ActiveWindow.LargeScroll Down:=-1
Range("T33").Select
ActiveWindow.LargeScroll Down:=-1
Range("Q2:S2").Select
Selection.ClearContents
Selection.ClearContents
Range("B6").Select
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
Sub ShowDialog()
UserForm1.LabelProgress.Width = 0
UserForm1.Show
End Sub
Thanks
Rockee:D :D :D
---
Message posted from http://www.ExcelForum.com/