As ever, you use this free program at your own risk.
The userform and code linked to by, or shown on, this page remain the copyrighted
material of Abbydale Systems LLC.. You are free to use the material but please
honor the copyrights.
The usual disclaimers apply.
A progress screen is useful to use with long running VB code within a spreadsheet, especially if screen updating is turned off. (Screen updating can negatively effect the performance of the VB code).
It may seem obvious, but there is no point in having a progress screen for short running code. So the first thing you need to do is to assess if there is an actual need for a progress screen! We use a progress screen just to show users that something is still happening 'behind the scenes', especially when using the SAP interface (AFE).
Of course displaying the screen will slow the program down just a little, but it is often better to show people that something is happening rather than have them wonder if something has gone awry or not.
Here is a screen shot of our Progress screen:
So off we go...this is how to do it.
The first step is to add the Progress userform to the workbook. This is done by opening the Developer tab in Excel.
If the "Developer" ribbon option is not displayed you will need to activate it.
A video detailing how to activate the "Developer" tab can be watched by clicking the button below.
Once the "Developer" tab has been activated you then need to design your Progress user form. If you prefer you can simply download ours from the link below and import it. To download our Progress form click the button below. We have also provided a sample demonstration code file for you to download if needed:
If you need instructions as to how to import the user form and the sample demo code (also available from the link on this page.) then the video demonstrating how to do this is available by clicking the button below:
The next step is to add a button to a spreadsheet which will run the code. If you don't know how to add a button, the video below will demonstrate how to do this.
You will notice that when we assign which code to run when the button is clicked that there are two procedures (RunIt & StartItOff) to chose from.
The one to select in order to process when the button is clicked is the one named StartItOff
RunIt is the main program, this is the one that will update the userform as it processes.
StartItOff is the process that will run when the button is clicked. It is this one that should be associated with the button.
If you examine the events associated with the userform you will see an event that is processed when the form is activated. The code within this event invokes the RunIt procedure.
StartItOff simply displays the userform, but because the button activates the userform, the RunIt procedure gets started.
One point to note is that the "Close" button on the userform is hidden until the very last display, however, you can always close the form by using the X.
Do not omit the DoEvents statement from the code, otherwise the userform will not update.
That is it. You now have a progress form.
This material has been used successfully by Abbydale Systems LLC. and to the best of our knowledge this material and any system(s) of which it is a part are operational as of the service level or date stated in the body of this material (if so stated). However, NO warranty is given or implied as to the accuracy of this material or any related material or systems, and no responsibility is assumed for any effect or modification directly or indirectly caused by the use of this material.
It is the responsibility of any user of this material to evaluate its usefulness to the user's environment.
Abbydale Systems LLC. does not guarantee to keep this or any related material current, nor does it guarantee to provide any corrections or extensions described by any users of this material or any corrections or extensions made in the future by Abbydale Systems LLC. itself.
