PROGRESS BAR QUESTION

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

K - 17 May 2008 11:41 GMT

Hi all, I have searched on this group and also many websites about
how to create Progress Bar. Few sites are below
http://www.erlandsendata.no/english/index.php?d=endownloaduserforms
http://j-walk.com/ss/excel/tips/tip34.htm
I am confused that how should I make my macro to work with Progress
Bar. I did try going instruction by instruction from above sites but
when I hit the button it goes all funny. For example if I have code
see below

CODE***********************************
Sub UPDATE()
Range("G6:L10000").Select
Selection.ClearContents
Range("G6").Select
ActiveCell.FormulaR1C1 = "=RC[-6]&RC[-5]&RC[-4]"
Range("H6").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-2]>0,RC[-2],IF(AND(RC[-2]="""",RC[-3]=""""),"""",RIGHT(RC[-3],
2)))"
Range("I6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]="""","""",
(RIGHT(RC[-1],LEN(RC[-1]))*1))"
Range("J6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-6]="""","""",RC[-6])"
Range("K6").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[1]C[-4],"""",RC[-4])"
Range("L6").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]="""","""",IF(SUMIF(R6C7:R10000C7,RC[-1],R6C10:R10000C10)=0,""ZERO
BUDGET"",SUMIF(R6C7:R10000C7,RC[-1],R6C10:R10000C10)))"
Range("G6").Select
Selection.Copy
Range("G7:G10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("H6:I6").Select
Selection.Copy
Range("H7:I10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("J6").Select
Selection.Copy
Range("J7:J10000").Select
ActiveSheet.Paste
Range("K6").Select
Selection.Copy
Range("K7:K10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("L6").Select
Selection.Copy
Range("L7:L10000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A5").Select
End Sub
******************************************
How can I make my above code work with Progress Bar. I want Progress
Bar to appear and macro to do its job and then Progress Bar should
disappear and during that proccess Progress Bar should show the
Progress from 0% to 100%. Can any friend explain me brefily that what
should i do or send me worksheet or code which can help. I did
download worksheet form the site http://j-walk.com/ss/excel/tips/tip34.htm
and tried adding my code but it didn't work. So if any friend can
help it'll be very helpful and much appreciated. Thanks

Reply to this Message

FSt1 - 17 May 2008 15:35 GMT

hi
john's progress meter(as do most other progress meters) is measuring a loop.
a counter counts the loops which is then compared to a total to get a
percent complete. the percent complete is used to update the progress meter.
john's code has a do nothing loop in it to illistrate the progress meter. to
use it, you must replace his do nothing loop with your loop.
which presents a problem. you code doesn't seem to have a loop. it seems to
be an inline process. therefore, you may not be able to use a progress meter
with your code.
most inline processes cannot use a progress meter.
instead, they place messages through out the code to tell the user what
stage the code is in. a small user form with a simple lable is used most and
as code prgresses, the lable is update with text stating what stage the code
is in ie stage1 of 4, stage 2 of 4, stage 3 of 4 ect. message boxes are use
also but not prefered since the user has to click the ok button for code to
continue. the status bar is also used ( and usually preferered) see this site
for examples of status bar usage.
http://www.cpearson.com/excel/StatusBar.htm
i have a macro that saves a file to 3 different places. at the end of each
save, a userform with lable is updated telling where the file is being saved
to next. the form even changes color at the start of each save.
with you code, you might have better luck with the above technique than with
a progress meter.

Regards
FSt1

> Hi all, I have searched on this group and also many websites about
> how to create Progress Bar. Few sites are below
[quoted text clipped - 63 lines]
> and tried adding my code but it didn't work. So if any friend can
> help it'll be very helpful and much appreciated. Thanks

Reply to this Message