ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Progress Bar (https://www.excelbanter.com/excel-programming/286351-progress-bar.html)

Rockee052[_2_]
Progress Bar
 
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:D :D :

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

Rob van Gelder[_4_]
Progress Bar
 
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/

Rockee052[_3_]
Progress Bar
 
Works great, thank you.


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

losmac[_2_]
Progress Bar
 
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!

-----Original 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/

.


All times are GMT +1. The time now is 02:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com