ProgressBar control in Userform

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

"Richard Buttrey" wrote:

> 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
>
>
> On Fri, 21 Oct 2005 08:21:02 -0700, "Charlie"
> <[email protected]> wrote:
>
> >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" wrote:
> >
> >> On Fri, 21 Oct 2005 07:27:07 -0700, "Charlie"
> >> <[email protected]> wrote:
> >>
> >> >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
> >> __________________________
> >>
>
> __
> Richard Buttrey
> Grappenhall, Cheshire, UK
> __________________________
>

Hello Charlie,

I created this Progress Bar for VBA. It works with VBA 5.0 (Sp2) and VBA 6.0. It has a full set of propeerties to allow you to change the color of the progress bar, and its text. It also has a message panel to allow you to display information about the progress. The bar is smoth and displays the percent completion in the center. I got tired of not 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 copy the code into it. The code is lengthy and complex, but the Progress Bar is very easy to use. I had to attach the code as a file because it is to long to display in the given message area.


Using the Progress Bar for VBA:

1) In the Declarations Section of the User Form, add the 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 the amount of time by setting the DisplayTime Property. The time is in 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 will 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 VBColor constant
TextColor - Change the Font Color of the Progress Percentage. RGB or 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 email [email protected].

Enjoy,
Leith Ross