 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
Which Controls to Use and When
Over the last five lessons we have looked at how to fill the Controls, the basics of the UserForm itself, how and when to validate the Controls. All this should lead to the obvious question, which controls should be use and for what purpose should we use them?
As with many things in Excel VBA, there is no hard and fast rule or definitive answer to this question. You will find that as you start using UserForms more frequently within your projects, each project itself will most likely be unique in many aspects. It is for this reason that we should treat each project on an individual basis. It is often worthwhile taking the time to plan your project before commencement and take things into account such as:
Who will be using the project
The experience of the people using the project
What people will be expecting from the project
What is the ultimate purpose of writing the project in the first place
Is the project going to be added to in the future (if unsure, best to assume it will be)
Taking these points into account, the next step can often be breaking the project into bite size chunks. I use this method myself and find it very helpful in staying focused on the ultimate purpose of undertaking the project in the first place.
You may often find yourself doing similar type projects with UserForms over and over again. If this is the case, you will probably also find that you will be using the same controls on a UserForm over and over again. For example, we can probably make the assumption that no matter what the project is, we would be using two command buttons, one called OK and one called Cancel. Obviously it would be good if we did not have to continually drag a command button off from the control toolbox and place it on to our UserForm re-size it and then give it the caption OK or Cancel. What we can do quite easily, is create a template page on our Toolbox, as described in Lesson 1, or in case you have forgotten, you simply right click on the Toolbox over the Controls page tab and select New Page. Right click over the new page selected, then select Rename, then type the word Templates and click OK. Now simply click back on the Controls page tab and with any blank UserForm inserted, drag over the commonly used controls, size them and alter their captions to suit, eg; Cancel, Control, etc. Once you have done this click back on your Templates page and drag each control from the UserForm on to it and save.
The only drawback with the abovementioned method is that we cannot have our template controls retain any code that we apply to them. For example, a cancel button would most often simply have the code:
Private Sub CancelButton_Click()
Unload Me
End Sub
Unfortunately though, as stated above, using a template as we have described will not retain the code or the name we have changed it to. It will basically only retain the caption we have given it. A very simple method which I use to overcome these problems is to simply open a new workbook, call it a meaningful name, eg UserForm templates, then insert any number of UserForms, and these UserForms will be the basis of each template. You would then give each UserForm a meaningful name which relates to the template that it will be. For example, you may create a template UserForm called "AddToList" and on it you might place an OK button, a Cancel button, a ComboBox, which would store the list of current names, and a TextBox where the user would type a new name to be added to a list. You would then go through and change all of the needed Properties for each of the Controls. You could then do the same for x number of other UserForms within the same Workbook and once finished you simply save the Workbook as a template. The next time you are required to do a project which would require any one of the template UserForms you have created, you would simply open this template Workbook and pick the most suitable UserForm with its Controls and use this as the basis for your project.
You will often find that you will be copying Controls from some of the other template UserForms within the same Workbook and only making minor alterations to their Properties and underlying code. Using this method can save many, many hours of repetitive type work. You can even go to the extent of using a consistent naming convention for ranges and apply these names to the appropriate Properties of the appropriate Controls. For example, ComboBoxes very frequently reference a list of data and you could have numerous ComboBoxes all with different RowSource properties such as "DynamicList1", "DynamicList2", etc.
The Right Controls for the Right Job
Excel has basically a Control for every possible job type. It is important to know which Controls to use for which job. It is fair to say that there are no hard and fast rules for this as each project usually has something which makes it unique. So, we need to at times use a Control and modify it to suit our specific needs. However, the following can be used as a guideline:
Will our project be using many Controls? If so, it is quite possible we will be using a MultiPage or TabStrip Control.
Will we be allowing the user to manually type in entries? If so, we will probably need a TextBox Control.
Will we be referring to a single column list on a Worksheet to allow the user to select from or reference? If so, a ComboBox may be required.
Will we be referring to a table on a Worksheet to allow the user to select from or reference? If so, a ListBox may be required.
Will we want the user to select one or more different options at the same time? If so, we may be needing CheckBoxes.
Will we want the user to only make one choice from a possible multiple choice? If so, Option Buttons may be needed.
Will we be wanting the user to turn something on and off? If so, a ToggleButton may be required.
Will we have a lot of text on our UserForm for the user to read? If so, a ScrollBar may be needed in conjunction with another Control.
Will we be wanting the user to increment a number or date up and/or down? If so, a SpinButton may be called for.
Will we want the user to be able to select specific ranges on a worksheet and have them passed back to another Control? If so, a RefEdit Control may be required.
Will we want to display a picture or logo on our UserForm? If so, an Image Control may be required.
Will we have a group of Controls which will be closely related in some way. If so, a Frame Control may be needed.
Just remember, these are guides only. Each project may have its own unique set of requirements.