progressbar for dummies

[kendel]

Hello,
There's so much information regarding this, and all I've found requires more knowledge than I have.
Please walk me thru it:
1. create the progressbar. Look like a file saving. simple, no text, no bells no whistles. Totally clueless here! VB editor? Worksheet code? Where to begin?
2. embed it into my macros, so it's only visible when I run the macros using a keyboard shortcut.

I thought it'd be simple but the more I researched it the more confused I am. Hehe, if you've run across me before you'll see that that is a common thread in all my inquiries.
The more I learn the more I find that I want to learn.

How about a MrExcel Vs Excel is Fun YouTube dueling podcast?!! Then there will forever be the definitive answer to the Progressbar question!!!


Thanks for any help,
Kendel (WinXP,Excel 2007 at work; Win7, Excel 2010 at home)

 

[bensonsearch]

Hi,
What is the progress bar going to be working off? there is a progress bar control but something needs to control it eg a webbrowser control.

the only other way would be to make some labels that apear one after the other to look like a progress bar.....

need more info on what you are using it with

 

[kendel]

Hello, Thanks for responding!
Here's what the macro does:

<TABLE style="WIDTH: 407pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=543><COLGROUP><COL style="WIDTH: 407pt; mso-width-source: userset; mso-width-alt: 19858" width=543><TBODY><TR style="HEIGHT: 99pt; mso-height-source: userset" height=132><TD style="BORDER-BOTTOM: #dadfe7; BORDER-LEFT: #dadfe7; BACKGROUND-COLOR: transparent; WIDTH: 407pt; HEIGHT: 99pt; BORDER-TOP: #dadfe7; BORDER-RIGHT: #dadfe7" class=xl69 height=132 width=543>turn off auto calculation and screen updating,
delete blank rows,
sort-subtotal by design,
insert time to complete formulas & formats
insert 2 rows between orders,
insert 'BRAND', screen counts, qtys, set-up times, print times, and cell formats using IF(VLookup) formulas.
insert set-up time & print time formulas and formats,
turn on auto calculation and screen updating

There are sometimes 300 rows that get looped so can take a min or so.
You asked what does it work off of, sry, don't know enough to answer.

Hope this will help.
Thanks again,
Kendel




</TD></TR></TBODY></TABLE>

 

[bensonsearch]

Sorry when i say what its working off, i meant what will it be monitoring/displaying the progress of?

if its the progress of a loop then no worries (like a counting loop) or progress of a webbrowser to a webpage etc

 

[Norie]

The code only takes a minute or so?

Are you sure you need a progress bar?

Perhaps you should look into making the code more efficient first?

 

[bensonsearch]

if in a loop then while in the loop put

On Local Error Resume Next
    ProgressBar1.Min = 0
    ProgressBar1.max = ProgressMax + 1
    ProgressBar1.Value = ProgressBar1.Value + 1

this will make it progress as the loop progresses

 

[kendel]

Hey Benson,
When I put the code into the macro, no progressbar.
Did we miss step 1? or is the progressbar self creating and I put it in the wrong place? I guessed just before 'next i'. I guess my options here are: before 'next i'
before 'if'
or after 'For i'.
Thanks again!
I am so dreaming that this will be as painless as it seems.
Kendel

 

[kendel]

Bump

 

[xenou]

The simplest progress bar is just to use the status bar:

[COLOR="Navy"]Sub[/COLOR] EasyProgressMonitor()
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    
    [COLOR="Navy"]For[/COLOR] i = 1 [COLOR="Navy"]To[/COLOR] 10
        Application.Wait Now + TimeValue("00:00:01")
        Application.StatusBar = "Running (" & i & " loops completed ...)"
    [COLOR="Navy"]Next[/COLOR] i
    Application.StatusBar = False

[COLOR="Navy"]End Sub[/COLOR]

 

[monirg]

Kendel;

I agree with Norie and xenou. Your code takes only a minute, so you don't need a progress bar or even a status bar!

That said and if you still want a progress bar, the following links provide a variety of different progress indicators that you can easily implement.
http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/
http://www.andypope.info/vba/pmeter.htm

Regards.