Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Progress Bar
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,236
Default 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/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default Progress Bar
Works great, thank you.


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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 44
Default 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/

.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Progress Bar PAL Excel Worksheet Functions 3 August 13th 08 03:28 PM
Progress Bar Jase Excel Discussion (Misc queries) 0 June 2nd 08 07:45 PM
PROGRESS BAR Ola2B Excel Discussion (Misc queries) 2 July 11th 07 08:13 PM
Progress bar [email protected] Excel Discussion (Misc queries) 1 January 6th 07 01:49 PM
Progress Bar Help Malcolm Excel Programming 2 November 25th 03 01:57 PM


All times are GMT +1. The time now is 11:54 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement