 |
|
|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
Prior to Excel 97, the only Controls available to the user on the spreadsheet were Controls from the Forms Toolbar. While these Controls certainly served a good purpose, they have nowhere near the flexibility of the Controls that are now available in later versions of Excel. Basically, the only thing that can be done with the Controls from the Forms Toolbar was assigning a pre-recorded or pre-written macro. Now in Excel 97 and 2000 the user has access to what are known as ActiveX Controls. The Controls on the Forms Toolbar are still provided for backward compatibility with earlier versions of Excel. While these Controls are really only for backward compatibility, I strongly believe that if the only reason a Control is needed on your Worksheet is to run a pre-recorded or pre-written macro, then a Control from the Forms Toolbar is a good choice. The reason being that ActiveX Controls found on the Control Toolbox Toolbar do carry a lot of overheads. This means if your Workbook contains a lot of Controls, it can have an adverse affect on the size of your Workbook if all the Controls are from the Control Toolbox Bar, ie; ActiveX Controls.
To display the Forms Toolbar, go to View>Toolbars>Forms and to display the Control Toolbox Toolbar, go to View>Toolbars>Control Toolbox.
What we will focus on here is the Controls from the Control Toolbox Toolbar. To start off with, I will run through a brief description and possible purpose of the commonly used Controls.
A Checkbox is generally used to allow the user to indicate a choice. By default, a checkbox has two possible states; TRUE or FALSE, or CHECKED or UNCHECKED. It is possible though, to set the checkbox so that it can actually have three states, TRUE, FALSE and NULL, or CHECKED, UNCHECKED and INTERMEDIATE.
Textbox
The Textbox Control is generally used to allow the user to type text into it. While the purpose of this Control itself is quite simple, it still allows the developer to set it in such a way that would best suit their purpose.
A Command Button is nearly always used to activate pre-written VBA code. The most common uses would be as an "OK" button, or "CANCEL" button. But they are certainly not limited to just these two options.
While an Option Button is very similar in function to the Checkbox Control, there should be a fundamental difference in the purpose that they are used for. An Option Button will allow the user to choose one of many similar mutually exclusive options. For this reason, they are usually displayed in what is known as "Groups". When they are in "Groups", it is only possible to choose one option of the same Group. A simple example of this may be that you may have five Option Buttons grouped, with each of them representing a colour. If the user selected Option Button 1, the colour they have chosen would be red. If they then chose Option Button 2, the colour they have chosen would then change to blue. In other words, Option Button 1 would become de-selected while Option Button 2 is selected. An Option Button has two states; they are TRUE or FALSE or SELECTED and DESELECTED and cannot be set to have three states like the Checkbox.
The ListBox is, as the name suggests, is a box that contains a list of items. The ListBox can be set to allow the user to select only one item at a time, or set to allow the user to select more than one item at a time. The list that it can display can be one column of data or more.
The ComboBox, while similar to the ListBox in that it will display a list for the user to choose from, will only ever allow the user to select one item at a time. It also can only display a one column list.
The ToggleButton's function is virtually identical to that of the CheckBox Control in that it allows the user to make a choice of two states, TRUE or FALSE. It can also be set to accept three states, TRUE, FALSE or NULL.
The SpinButton Control is used to allow the user to increment a numerical value. A SpinButton would generally be used when we only want the user to cycle through a range of numbers set by us.
The ScrollBar is almost identical in function to the SpinButton, except that as the name suggests, it also has a bar that scrolls. While a SpinButton can only change a value, by hitting the up or down arrow, the ScrollBar can do this but also allows the user to move the ScrollBar itself.
The main function of the Label Control is to display static text. While this is it's main function and it is rarely used for much else, it can be set in such a way as to be used like a TextBox.
The Image Control is used to house an image or picture; eg; bitmap, metafile, etc., on the Worksheet. By placing an image in the Image Control, it allows us to determine how the image will be displayed eg; clipped, stretched, tiled etc.
When we use Excel we are actually most of the time using an ActiveX Control to change, alter, display, increase, decrease, select, choose etc our choice. To see what I mean activate the Options dialog box (Tools Options). Now click around on each page tab and you will see most of the Controls mentioned above. The Checkbox is probably the most frequent Control on this dialog box. In case you are wondering the square boxes are CheckBoxes, while the round ones are OptionButtons. The rectangular boxes with the drop arrow on the right with a choice displayed are ComboBoxes. If you select the Edit page you will see a SpinButton that sets the Decimal places within a TextBox. On the Custom List page you will see two ListBoxes. Have a click around with these as they will give you a good idea of the type of circumstance that a particular Control would be used for.
The Controls discussed above are only the most commonly used Controls from the Control ToolBox Toolbar. It does, however, allow us if needed to access many more Controls. To see a list of these, there is a button at the very bottom of the Control ToolBox Toolbar, which is called More Controls. It has a symbol of a hammer and a spanner in the shape of an "X". If you click on this button, you will see a list of all other available Controls. For obvious reasons, we won't be going through any of these at this stage.