ProgressBar control in Userform

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

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
__________________________

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
> __________________________
>

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
__________________________

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...

"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
> __________________________
>

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

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

"Charlie" <[email protected]> skrev i melding
news:[email protected]...
> Hi,
>
> Does anyone have any examples of using the ProgressBar control (v. 6.0) on
a
> Userform?