|
|
This series of x lessons will introduce UserForms as implemented for Visual Basic in Excel. It is for developers and programmers, progressing from basic topics in the early lessons to more advanced topics in the later lessons. The viewpoint of the client and user is considered where applicable.
UserForms were first introduced into Excel in Excel 97. Prior to this Dialog sheets were used. These are still available by right clicking on any Worksheet name tab and selecting Insert>MS Excel 5.0 Dialog. This will insert a Dialog sheet that has a Dialog box on it and by default the Forms toolbar will appear. However, they are only available for backward compatibility with previous versions of Excel and should only be used for this reason. The UserForms that are now available provide much greater flexibility for both the user and developer. The UserForm is still at times referred to as a Dialog box.
UserForms are Visual Basic objects, with properties, methods, and events.
Perhaps the best result of the introduction of UserForms is not so much the Form itself as the ability for it to accept ActiveX controls. These controls provide far more flexibility than the controls available on the Forms toolbar (OLE custom controls). They have far superior event handling that allows us to respond to a user’s actions in ways that were previously not possible. Specific code can be written to be run when the user clicks the control, enters, exits, double clicks, right clicks, enters text, and much more. Basically, Excel is able to capture and react to any action the user takes.
Controls are also Visual Basic objects, with properties, methods, and events.
When to Use a UserForm?
There is no definitive answer to this question, as the use of the UserForm allows such flexibility that one can be used in a wide range of situations. It can be for something as simple as creating your own message box, and for something as complex as replacing the entire Excel interface. You will no doubt find that you will use Excel UserForms more and more as you become more comfortable with them. The only restriction will be your own imagination and knowledge of the controls that can be used on them.
If you want to give your projects a professional and consistent look and feel, then UserForms will certainly do this. However, having said this, be careful you don't use a UserForm for this reason only! While a professional look and feel is great, the professionalism soon dissipates if the code is not there to support it. So my advice is, if you feel confident enough to use a UserForm, then there is no reason why you shouldn't.
Having encouraged you to use a UserForm though, you should be aware that UserForms often require a lot more coding than normal. This is because you must ensure that all user actions are handled, that data are valid, and that data are properly stored in a collection area (data base). While this probably sounds like a relatively simple task, there are a lot of angles to cover. Unfortunately, users make random button clicks and enter invalid data!
What to Use a UserForm For?
There is no definitive answer to this question, either. Common applications are for entering and validating data, for displaying data, for setting conditions, for running programs, for informing the user of errors or exceptions, and for guiding the user through dealing with errors and exceptions. All of these are accomplished by inserting code into the Private Module of the UserForm. Code that validates what the user enters is followed by code to save the validated information to a spreadsheet in the correct place, and to format it correctly. When the UserForm is programmed properly, it is virtually impossible for erroneous data to be entered, while at the same time making data input for the user very easy indeed.
UserForms and their associated controls can be seen extensively throughout Excel. Activate any menu item that displays a Dialog box and we are looking at a UserForm. This can be a useful way for us to decide if a UserForm is suited to the task at hand. It can also be used to guide us in which control to choose for a particular situation.
As I have mentioned above, as you become more comfortable with UserForms and their controls, you will start to use them for a variety of tasks as simple as instructing a User, through to creating your own wizards that will guide the user through complex tasks.
Common Controls
To help envision the application in the next step, it is necessary to know the common controls and their functions. These controls can be located any place on the UserForm.
|
Control |
Description |
|
Label |
Displays text that cannot be edited by the user, such as field labels, notes, and instructions. |
|
TextBox |
Displays a single field of text the user enters and that the user can edit. |
|
ListBox |
Displays a list of items from which the user can select, optionally with multiple fields. |
|
ComboBox |
Combines the function of a TextBox and a ListBox. |
|
CheckBox |
Displays an option that can be turned on or off. Any number of a list of checkboxes may be selected. |
|
Frame |
Used to group other controls, especially OptionButtons. |
|
OptionButton |
Similar to a CheckBox, but only one OptionButton in a group can be "on" at one time. To create a group of OptionButton controls, place a Frame control on the UserForm first, then draw the OptionButton controls on the Frame. |
|
CommandButton |
Displays a button that's clicked by the user to carry out some action such as Save or Enter. |
|
ToggleButton |
A button that can be either "up" or "down," permitting the user to select and deselect items. It looks like a CommandButton but acts like a CheckBox. |
|
TabStrip |
Displays two or more tabs at the top of the control, permitting the user to select one. It is for displaying small amounts of the same information for each tab. |
|
MultiPage |
Displays a large amount of information split into two or more pages. |
|
ScrollBar |
Displays a vertical or horizontal scrollbar on the UserForm. |
|
SpinButton |
Increments and decrements numbers by clicking with the mouse. |
|
Image |
Displays an image. |
Many other controls are available, and will be discussed in Advanced Topics.
Gathering Requirements
To design a good UserForm, it is essential that someone must develop a vision for how the application is to look and how it is to be used. This can be the customer, the programmer, or a system analyst. The programmer must have this vision in mind when coding and when testing. Vision and requirements are best established before Excel is even opened.
Everyone has used preprogrammed forms, both for commercial software packages and with web pages. These are excellent sources for ideas for the designer. Users become accustomed to finding functions organized in logical fashion, with consecutive actions sequentially located on the form.
These are many of the questions that need to be answered, grouped by topic.
The users of the project
What is the purpose of writing the project in the first place?
What is the experience of the people using the project?
What will people be expecting from the project?
Future considerations
Is the project going to be enhanced in the future (if unsure, it’s best to assume it will be)?
Will this work be incorporated into a larger project?
Entering and validating data
For each field, will the user enter free-form data, or be restricted to choosing from a list?
For free-form data, will each field be text or numbers?
For free-form numbers, what range of values will be allowed?
Will there be visual feedback confirming the successful validation and storage of data?
Displaying data
How many records will be displayed?
How many fields or columns will be displayed?
Saving data
Will data be saved in this file or an external file?
Can they be saved in a flat file, or will cross-reference or lookup tables be needed for some fields?
Editing data
Can saved data be edited?
How will data to be edited be located and displayed?
Setting conditions
What variables affect the running of the programs?
What values can each variable take?
Running programs
Will programs be initiated by clicking buttons or by changing conditions?
Will completion status be displayed?
Is there output to additional files?
Will output files be located in a fixed or specifiable folder?
What are the output file naming conventions?
Informing the user of errors or exceptions
What should be the appearance of the message box?
Will display of errors offer correction suggestions?
Will standalone or onboard error logs be required?
Will errors need to be reported via Internet?
Guiding the user through dealing with errors and exceptions
Set up invalid data for editing.
Inform user where to correct source of error, such as a non-existent save folder.
For other errors, give the user as much information as is available.
Download User Requirements Checklist in the attached file UserForms Lesson 1A 2007. This is the above checklist in a standalone file.
Envision the Application
The vision of how the UserForm will be used includes its overall look and some details about how the various controls will look, be arranged, and be used. A good approach is to use a piece of paper with an old fashioned pencil with an eraser. A control of some kind will be needed for each item identified in the requirements. The most user-friendly order of controls will probably be left-to-right or top-to-bottom, but this may not be true if the client is left-handed or of a right-to-left language such as Hebrew or Arabic. Leave extra room everywhere, both in the sketch and when first creating the layout. It is far easier to add additional controls, move controls around, and make the layout neat and compact at the end, than it is to enlarge and make more room many times during the design.
If the client requires an identifying banner or proprietary icon, it must be placed and sized first. Then place the various controls. Sketch the biggest items first, such as ListBoxes and ComboBoxes, labeling each above the upper left corner. Second, sketch in TextBoxes to be used for data entry, with labels above or to the left. If the data is to be validated, then at the bottom or side of the sheet sketch a MsgBox or another UserForm that will inform the user of the invalid data and what to do to correct the problem. Then sketch in buttons such as Enter, Submit, OK, or Cancel, and write the function inside the button. Lastly, sketch any small controls such as CheckBoxes, OptionButtons, or SpinButtons. Don’t worry about whether you have every detail at this point. Many details will become apparent as you do the layout, the coding, and the testing.
If the application will have multiple tabs or cascading pages, use one sheet of paper for each tab or page.
Now Where?
Once all the preparation is complete, the next steps are:
Create and rename the new UserForm (Lesson 2).
Create, size, position, and rename the controls (Lessons 2, 3, and 4).
Submit the layout to the client for preliminary feedback.
Write initialization code to populate ListBoxes and ComboBoxes (Lesson 5).
Write code to load the new UserForm (Lesson 5).
Debug the initialization (Lesson 6).
Write code to handle user actions (Lesson 7).
Test user action handling code
Write code to validate user inputs (Lesson 9).
Test input validation code.
Set the tab order for controls requiring user input and controls offering user selection choices.
Evaluate the application for ease of customer use. Correct any problems found.
Polish the look by moving and resizing controls and UserForms, and by picking appearance properties.
Evaluate the application execution speed. Locate and improve performance problems.
Submit the project to the client.
Make corrections as needed.
Celebrate in an acceptable manner.
These steps do not always need to be in this order, and several of the steps
involving coding and testing will occur reiteratively.
|
|
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.