Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ProgressBar control in Userform
On Fri, 21 Oct 2005 07:27:07 -0700, "Charlie"
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 __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ProgressBar control in Userform
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" 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 __________________________ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ProgressBar control in Userform
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" 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" 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 __________________________ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ProgressBar control in Userform
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" 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" 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 __________________________ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" 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" 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 __________________________ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ProgressBar control in Userform
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. [b]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 . Enjoy, Leith Ros +------------------------------------------------------------------- |Filename: ProgressBarVBA.zip |Download: http://www.excelforum.com/attachment.php?postid=3936 +------------------------------------------------------------------- -- Leith Ros ----------------------------------------------------------------------- Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846 View this thread: http://www.excelforum.com/showthread.php?threadid=47825 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ProgressBar control in Userform
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" skrev i melding ... Hi, Does anyone have any examples of using the ProgressBar control (v. 6.0) on a Userform? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ProgressBar control displays erratically on Open | Excel Worksheet Functions | |||
ProgressBar Control | Excel Discussion (Misc queries) | |||
Control Sequence from Userform Control | Excel Programming | |||
Microsoft ProgressBar Control Version 6 | Excel Programming | |||
Microsoft ProgressBar Control Version 6 | Excel Programming |