Microsoft Office Tutorials and References
In Depth Information
Displaying a Progress Indicator
Following are some key points about this more sophisticated version:
h The UserForm has a check box (Auto Update). When this check box is selected, the
UserForm is updated automatically. When Auto Update isn’t turned on, the user can use
the Update button to refresh the information.
h The workbook uses a class module to monitor two events for all open workbooks: the
SheetSelectionChange event and the SheetActivate event. As a result, the code
to display the information about the current cell is executed automatically whenever
these events occur in any workbook (assuming that the Auto Update option is in effect).
Some actions (such as changing a cell’s number format) do not trigger either of these
events. Therefore, the UserForm also contains an Update button.
Refer to Chapter 29 for more information about class modules.
h The counts displayed for the cell precedents and dependents fields include cells in the
active sheet only. This is a limitation of the Precedents and Dependents properties.
h Because the length of the information will vary, VBA code is used to size and vertically
space the labels — and also change the height of the UserForm if necessary.
Displaying a Progress Indicator
One of the most common requests among Excel developers involves progress indicators. A
progress indicator is a graphical thermometer-type display that shows the progress of a task, such as
a lengthy macro.
In this section, I describe how to create three types of progress indicators for
h A macro that’s not initiated by a UserForm (a stand-alone progress indicator).
h A macro that is initiated by a UserForm. In this case, the UserForm uses a MultiPage
control that displays the progress indicator while the macro is running.
h A macro that is initiated by a UserForm. In this case, the UserForm increases in height
while the macro is running, and the progress indicator appears at the bottom of the
dialog box.
Using a progress indicator requires that your code is able to gauge how far along your macro
might be in completing its given task. How you do this will vary, depending on the macro. For
example, if your macro writes data to cells and you know the number of cells that will be written
to, it’s a simple matter to write code that calculates the percent completed. Even if you can’t
accurately gauge the progress of a macro, it’s a good idea to give the user some indication that
the macro is still running and Excel hasn’t crashed.
Search JabSto ::

Custom Search