ProgressBar control in Userform

[Charlie]

Hi,

Does anyone have any examples of using the ProgressBar control (v. 6.0) on a
Userform? When I show the UserForm from Workbook_Open the form is awaiting
user input. I suspect I need a timer control to perform the bar update (plus
I need to know how to update it.) But I can't seem to find the timer control
in the Additional Controls toolbox? What is it called? Can you point me in
the right direction?

TIA
Charlie

 

[Richard Buttrey]

Hi,

Does anyone have any examples of using the ProgressBar control (v. 6.0) on a
Userform? When I show the UserForm from Workbook_Open the form is awaiting
user input. I suspect I need a timer control to perform the bar update (plus
I need to know how to update it.) But I can't seem to find the timer control
in the Additional Controls toolbox? What is it called? Can you point me in
the right direction?

TIA
Charlie

I've got something called a CoolBar control , but not a Progress Bar
control (v6.3), however this says it's not licensed.

The work around seems to be two text boxes in contrasting colours, one
of which remains fixed width. The other starts out as width 0, which
is upated through a DoEvents command and where the width is
progressively increased. This then appears like the standard progress
bars.

HTH

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Charlie]

I did find and download something similar to that method -- one picturebox on
top of another, two different colors, the top one growing like you describe,
but the key to it working was the Timer event which updated the picturebox
size at regular intervals. Worked great, but my problem is I can't find the
Timer control in my VBA toolbox. And I looked in the Additional Controls
toolbox too!

 

[Richard Buttrey]

OK. Using this approach there is no 'Timer event' as such. You need to
calculate the width of the text box by including a calculation in the
part of your code which loops round,

For instance in the For Next loop you will know the "To' value which
will end the loop. Assuming the loop counter is say "x" and the To
value is say 100
i.e. For x = 1 to 100, and the full width of the text box is 250, in
your main loop you'll need to call a progress bar procedure (passing
the x value if x is not a Public variable), with something like

Sub ProgressBar
MyTextControl.Width = x / 100 * 250
End sub

HTH

I did find and download something similar to that method -- one picturebox on
top of another, two different colors, the top one growing like you describe,
but the key to it working was the Timer event which updated the picturebox
size at regular intervals. Worked great, but my problem is I can't find the
Timer control in my VBA toolbox. And I looked in the Additional Controls
toolbox too!

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Charlie]

That would work except the purpose of the Timer is to allow for updating the
control without the user first having to click a "Go" button or something
like that.

The UserForm1.Show statement displays the form but then waits for user
input, such as clicking a button, before it will begin executing subs. My
workbook automatically executes macros from Workbook_Open without the user
needing to click anything.

The example I found does just that, the form shows itself and then begins
updating the progress bar automatically. If I could just find the Timer
control in my toolbox...

 

[Charlie]

Wait... I think I can show the form vbModeless and do it your way. Let me
give it a try. Thanks for the help.

 

[Leith Ross]

Hello Charlie,

I created this Progress Bar for VBA. It works with VBA 5.0 (Sp2) an
VBA 6.0. It has a full set of propeerties to allow you to change th
color of the progress bar, and its text. It also has a message panel t
allow you to display information about the progress. The bar is smot
and displays the percent completion in the center. I got tired of no
having a control in VBA to do this, and I am sure you feel the same.

You will need to insert a Class Module into your project and then cop
the code into it. The code is lengthy and complex, but the Progress Ba
is very easy to use. I had to attach the code as a file because it is t
long to display in the given message area.


USING THE PROGRESS BAR FOR VBA

1)[/B] IN THE DECLARATIONS SECTION OF THE USER FORM, ADD TH
FOLLOWING...

PUBLIC PROGRESSBAR1 AS NEW PROGRESSBARVBA

*2)* IN THE USER FORM'S ACTIVATE EVENT ADD...

PROGRESSBAR1.INITIALIZE

*3)* TO DISPLAY THE PROGRESS OF AN OPERATION...

PROGRESSBAR1.PERCENT = X/Y 'WHERE X IS THE LAGER VALUE

*4)* TO DISPLAY A MESSAGE ABOUT THE PROGRESS...

PROGRESSBAR1.MESSAGE = \"WHATEVER YOU TO SAY\"

THIS WILL DISPLAY THE MESSAGE FOR 1/4 OF SECOND. YOU CAN CHANGE TH
AMOUNT OF TIME BY SETTING THE DISPLAYTIME PROPERTY. THE TIME IS I
MILLISECONDS. THIS PAUSES PROGRAM EXECUTION TO DISPLAY THE MESSAGE.

THE PROPERTIES ARE

BarColor - This can be an RGB value or VBColor constant
DisplayTime - Time in milliseconds (1/1000 of second = 1 millisecond
to display a message. Set to zero to make the message static. This wil
not pause the program.
hWnd - Window Handle of the ProgressBar (Used in API calls). Read Only
Message - Any information you want to display to the user.
MessageColor - Change the Font Color of the message. RGB or VBColo
constant
TextColor - Change the Font Color of the Progress Percentage. RGB o
VBColor constant

THE METHODS ARE

Clear - Clears both the Progress Bar Display and the Message Display
ClearMessage
ClearProgressBar
Refresh - Repaints both the ProgressBar and the Message

If you have any problems or need more information, contact me via emai
(e-mail address removed).

Enjoy,
Leith Ros

+-------------------------------------------------------------------
|Filename: ProgressBarVBA.zip
|Download: http://www.excelforum.com/attachment.php?postid=3936
+-------------------------------------------------------------------

 

[Harald Staff]

Hi Charlie

I know this isn't what you want to hear, but I'll try to get some sleep
tonight anyway:
"Additional controls" is trouble and nothing but. It's known as "dll hell",
invented at a time where disk storage was ridicolously expensive and
programmers paid money to work. If there is an alternative way to do what
you want done then that's by default a better way.

Best wishes Harald