Progress Bar

[Rockee052]

Hi,

I have made a progress bar based on a macro, the macro just clears th
contents of some selected ranges. The problem I have is the th
progress bar does not show the percent complete
until its finished clearing the contents. Can anyone help? Here is wha
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 :

 

[Rob van Gelder]

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").FormulaR1C1 = "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]

Works great, thank you.

 

[losmac]

After this line:
UserForm1.Show
nothings happend, because every UserForm is showing in
dialog mode. So...
1) - create event UserForm_Activate()
2) - In UserForm_Activate procedure body insert your code
to clearing cells and upgrades progress bar and after all
to unload form

Try to write code more "clean" and "simple".
For example:

Range("B6:H10").Select
Selection.ClearContents

is the same:
Range("B6:H10").ClearContents

To work corectly and to clear cells in corect worsheet You
need to set work context, like this:
With ThisWorkbook.WorkSheets("AAA")
.Range("B6:H10").ClearContents
...
End With

Have a nice day!