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.
TextBox and SpinButtons
The Control Toolbox within the Visual Basic Editor by default will hold only the most commonly used controls. With just these Controls you will often find that they can be manipulated and programmed to do most if not all of what would be expected of most projects. Take for example the TextBox Control (possibly the most frequently used Control). Just because it is called a TextBox, by no means means that we are limited to only using text in this Control. While the default for this Control is to return text, it can also just as easily return numeric values.
Let's now look at some examples of how we could use a TextBox.
Most often a TextBox Control would be used to collect typed information from a user. In previous lessons we looked at the fact that the user may mistype their information so we need to include as much validation checking as we possibly can. Obviously if the user will be typing their name there is no validation check that we can really do unless of course we have a list of all possible names that would be entered into it in which case we would end up using a ComboBox to store these names. Let's assume for a moment that we are going to use our TextBox in this instance to collect a numeric value from the user. We could simply allow the user to type in a number and/or use the SpinButton Control to increment up to the desired number.
The SpinButton Control simply increments and decrements a number. The default Property for a SpinButton is the Value Property, while the default Event for a SpinButton is the Change Event.
Try this simple example to help better understand the SpinButton Control.
Insert a UserForm
On it place a TextBox
To the right of the Textbox place a SpinButton
Right click on the SpinButton and select Properties to display the Property Window for the SpinButton
Take a moment to look through these properties and you will notice that most of them are common to many other Controls.
Double click the SpinButton Control and within the default Change Event type TextBox1.Value=SpinButton1.Value
Now run your UserForm and user your SpinButton control to increment or decrement a number.
You will notice that by default the lowest value we can reach is 0, with the highest value being 100. If you have not guessed already, this can be easily changed in the Properties window for the SpinButton under the Max and Min Properties. So let's go into this spot now and change this minimum and maximum to any two numbers. While you are there, also notice the Property called SmallChange. This simply tells our SpinButton what value to increment by. The default is 1, but we can change this to any value that fits within the scope of a Long, ie; -2, 147, 483, 648 to 2, 147, 483, 647. But having said this the recommended range of values ( (according to Excel's help) is from -32,767 to 32,767. Let's change the SmallChange Property of the SpinButton to 2. Now run your UserForm again and use the SpinButton to increment or decrement your number. This time you will be incrementing up by a step value of 2 until you reach the value set in your Max Property and down until you reach the Min Property set. This is the use of the SpinButton in possibly its simplest form. But don't confuse this as being the same as the SpinButton being a very simple control and not having much scope of purpose. If you think about it, the whole basis of computers and Excel especially are simply built on numeric values. By this I mean that we could use the SpinButton Control to allow the user to do almost anything and you will be surprised at how many users find it more convenient to increment up/down to a number rather than type it. Not only does this help the User, but it can also aid us greatly in the fact that we can validate the TextBox storing the number to fit between a certain range and pattern without having to put in some lengthy code to validate the number chosen.
To ensure that the user cannot type into the TextBox a specific number, all we need to do is either change the Enabled Property of the TextBox to FALSE and/or the Locked Property. Once we have done this we can assume as safely as possible that the number chosen by the user using the SpinButton will meet any given criteria that we have determined.
Let us now assume we have a scenario where we have a TextBox always defaulting to today's date and we want the user to be able to increment that date by one day up to any day one year ahead. To allow the user to simply add say 21 days to a date, is fraught with potential disasters. Let us use an example now with our TextBox to see how we can use the SpinButton to do this safely.
Double click the UserForm
Change the default Click Event to the Initialise Event
Within here place TextBox1.Value = Format(Date, "ddd d mmm yyyy")
Above your TextBox add a Label Control and change its Caption Property to read today's date.
Now within the SpinButton1_Change Event change our existing code to TextBox1.Value=Format(Date + SpinButton1.Value, "ddd d mmm yyyy") and below this type Label1.Caption=SpinButton1.Value & " day(s) from now"
Change the Min Property of the SpinButton to 0 and the Max Property of the SpinButton to 365
Increase the TextBox width so it will display the date in the format chosen
Run your UserForm
As soon as the UserForm shows our TextBox will display the current date including the name of the current day. Now use your SpinButton control to increment the date by 1 day at a time. You will notice that the date in the TextBox will increment by one day each time. Using this method you can ensure that the User will not type an invalid date that is not recognized by Excel or miscalculate the date from the number of days chosen. In fact, it would be impossible to do so.
Calendar Control
One Control that Excel has which is not shown by default is the Calendar Control. This is a very handy Control to use when dates are required to be collected from the user. Not only does it make our life easier but also the user, as they can visually see a real calendar.
What I would like you to do using all available resources is create a small project that would allow a user to nominate certain dates and have them passed back to specific cells as shown in the attached Workbook. I am purposely NOT telling you how this Control works as it is very important at this level you are able to work out problems and overcome them.
Once you have had a go at this go here for some examples etc of this great Control:
http://www.microsoft.com/exceldev/articles/CalExcel.htm
©2002 ozgrid - microsoftexceltraining - David & Raina Hawley. All right reserved