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.

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.

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.

Let's now go through the Toolbar and see how it would be used.

By default, the Control ToolBox Toolbar is what is known as a "floating toolbar".  We can change this quite easily if it has not been done already by either dragging it to the top of the Workbook where the other toolbars are located and releasing, or by clicking the blue title bar, in this case "Control Toolbox".  When we do this, the toolbar becomes what is known as "docked".  Whether you have the toolbar docked or floating is entirely up to you.

Other than the Controls already discussed, you will notice that there are three buttons represented by symbols at the top of the Control Toolbox Toolbar.  As with all toolbars, to get the name of the particular button, simply wave your mouse pointer over it.

The first one is what is called "Design Mode".  This is represented by a symbol of a ruler, triangle and pencil.  If you click this button you will set the Control Toolbox Toolbar and any Controls on the Toolbar or the active Worksheet to "design mode".  When they are in design mode they have no functionality other than allowing us to manipulate them in a way which suits our purpose.  You will probably have noticed that when you clicked the "Design Mode" button another small floating toolbar appeared.  By clicking this you will exit design mode.

The next button is called "Properties" and is represented by a picture of a sheet of paper and a hand pointing to it. Clicking this button will display what is known as the "Properties" window of the selected Object.  An Object in this context would be a Control from the Toolbar.  If no Control is selected, the Properties window will be for the active Worksheet itself.  To close the Properties window, simply click the X in the top right hand corner of it.

The third and final button is called "View Code" and is represented by a picture of a sheet of paper and a magnifying glass.  Selecting this button will take us into the Visual Basic Editor (VBE) of the current Workbook. By default, the module that will be displayed will be the Private Module of the active Worksheet.  The reason for this is that all ActiveX Controls, once attached to a Worksheet become an Object of the Worksheet Object itself.

As you have no doubt realised, all Controls have their own Properties, some of which are unique to the particular Control itself, while others are common across all Controls.  Let's use a Checkbox to look at the Properties of a Control.  The first thing we need to do is attach (or embed) a Checkbox to a Worksheet.  This is simply done by a single left click on the Checkbox Control and then a single click on the Worksheet where you wish the Control to appear.  You will also notice that by doing this, Excel will automatically put us into "Design Mode". We can now access the Properties of this Control in two ways. The first is by selecting the Properties button from the Control Toolbox Toolbar and the second is by right clicking on the Control and selecting "Properties".

The Properties window has two tabs on it, both tabs contain the same properties, the only difference is that one is "Alphabetic" and the other is "Categorised". I will use the second tab (Categorised) to look at the most likely used Properties. When we change the Property of any Control in this way, we are changing it in what is referred to as "design time". If we change the Property of a control during the execution of some VBA code we are changing it in what is referred to as "run time". To alter the Property of any Control we either type the Property in the box to the right of the Property or select it from a drop down list of choices.

Let's now look at some Properties. It is important to note that the all Properties of a Control can only be changed while in "Design Mode" or at "Run Time" via VBA.

As the name suggest these Properties will change the appearance of the Control. Most of these Properties are common to all Controls. The exception is the Caption Property.

This will change the position of the Checkbox from right to left. In other words it will reverse the Control.

This simply allows you to choose from any of the colors available to Excel. Changing this will change the color of the Control.

This will give you two choices of having either a Transparent Control or Non-transparent (Opaque)

This is where you would type a Caption that you want the user to see. The default is always the name of the Control and a number. The numbers will follow in sequence, ie Checkbox1, Checkbox2, Checkbox3 etc

ForeColor refers to the Font color of the Caption.

This is where you can alter the appearance of the chosen Control. We can make it either Flat or Sunken(3D effect). Some Controls have up to six SpecialEffects.

As mentioned above this is where you would set it's default value. TRUE would make it appear checked, while FALSE would make it appear unchecked. We could also set it to NULL, but only if we have set it's "TripleState" to TRUE. We will see this soon.

It is under this Category that we can set how the Control will act under certain circumstances. With the exception of "TripleState" these Properties are common to most Controls.

In the case of a Checkbox this would only effect the size of the Checkbox if the Caption was altered. It takes a Boolean value of either TRUE or FALSE. Let's say we set it to TRUE then changed the Caption to a very long word, the CheckBox would automatically change size to accommodate the new text. If we left it set to FALSE it would not.

This has three settings, Left, Right and Center. In the case of the CheckBox it only applies to the Caption. If the Control was a type that did not have a Caption Property, it would apply to the Text or Number it holds.

As discussed this is only available to a CheckBox or ToggleButton. It takes a Boolean, TRUE or FALSE. When set to TRUE the Control Value can be either TRUE, FALSE or NULL. When set to FALSE the Value can only be TRUE or FALSE.

This is exactly the same as setting a cells Alignment Property to "Wrap Text" under the Format Cells dialog. As with the TextAlign, if the Control has a Caption Property it applies only to the Caption. If the AutoSize Property is set to TRUE the WordWrap will not behave as expected.

This is the only Property here and as the name suggest it allows us to alter the Font. It is important to note that the Font of any Control cannot be changed at Run time. Again, if the Control has a Caption Property it applies to the Caption only.

This is always the largest category for a Control and houses all the Properties that do not fit under the above Categories. I will not describe all of these as some are rarely used. For the ones I do not describe you can have Excel display the Help Topic for the selected Property by selecting it and pushing F1

Common to ALL controls, this is where you define a name for your Control. Once the Name is set you then use this to identify your Control. No two Controls on the same Worksheet can have the same name. The Name follows the same naming convention as for named ranges. As with the Caption, the default name is always the type of Control followed by a number. It is good practice to name your Controls in a descriptive manner and also include some method of being able to identify the Control type by looking at the Name. For example if you have a CheckBox that is used to change the Back Color of a cell you might use: ChBxCellColor. The name can only be set at Design time.

This is where we can disable a Control so that a user cannot access it in any way. It takes a Boolean and if set to TRUE the Control becomes disabled. It should be noted that we can still access the Control when it is Disabled via VBA. In other words it only Disables the Control for the user.

This Property only applies to OptionButtons. By default the GroupName will be the Worksheets Tab name at the time the Control was added (embedded) to the Worksheet. If the Sheet Tab name is changed after the Control is added the GroupName will not reflect the change. A set of OptionButtons that all have the same GroupName will only allow the user to set the Value Property to TRUE (selected) of one OptionButton at one time. Why the CheckBox has this Property I honestly do not know!

This determines the position of the Control on the Worksheet. Zero would place the Control in the very top left of the Worksheet, ie cell A1.

This would be any cell that you nominate to store the current Value Property of the Control, eg TRUE or FALSE.

Very similar to the Enabled Property and is usually set to work in conjunction. See the text below from Microsoft's Help:

The Enabled and Locked properties work together to achieve the following effects:

• If Enabled and Locked are both True, the control can receive focus and appears normally (not dimmed) in the form. The user can copy, but not edit, data in the control.
  
  

• If Enabled is True and Locked is False, the control can receive focus and appears normally in the form. The user can copy and edit data in the control.
  
  

• If Enabled is False and Locked is True, the control cannot receive focus and is dimmed in the form. The user can neither copy nor edit data in the control.
  
  

• If Enabled and Locked are both False, the control cannot receive focus and is dimmed in the form. The user can neither copy nor edit data in the control.

The ability to receive mouse clicks or keyboard input at any one time. In the Microsoft Windows environment, only one window, form, or control can have this ability at a time. The object that "has the focus" is normally indicated by a highlighted caption or title bar. The focus can be set by the user or by the application.

This takes a Boolean and when set to FALSE the Control is no longer Visible if we are not in Design Mode.

All the Properties that are mentioned above are used with most Controls with exception of Caption, TripleState and GroupName. What we will do now is look at the most likely used Properties which are often unique to certain Controls. Let's start with the ListBox.

When you have a ListBox it can display more than one Column of data at a time as well as more than one Row. When you set the BoundColumn for a ListBox it will determine the Column that is returned as the current value for a ListBox. Lets say we had a ListBox with three Columns of data and 10 Rows. If we set the BoundColumn to 2 and then selected the fifth row the current value of the ListBox would be whatever is on Row 5 - Column 3. The reason it would be Column 3 and not Column 2 is because the first Column is always 0 (zero) as is the Row.

This where we can set the number of Columns to display in our ListBox. Setting this to 0 (zero) means no Columns will be displayed. To display all available Columns you set this to -1 If the ListBox is *unbound to a **datasource the limit for ColumnCount is 10 ie; 0-9

*unbound

Describes a control that is not related to a worksheet cell. In contrast, a bound control is a data source for a worksheet cell that provides access to display and edit the value of a control

**data source

The location of data to which a control is bound, for example, a cell in a worksheet. The current value of the data source can be stored in the Value property of a control. However, the control does not store the data; it only displays the information that is stored in the data source.

Must be either TRUE or FALSE (Boolean). Setting it to TRUE will display a single row of Column headings in your ListBox. These cannot be selected if the first row of data is used as the ColumnHeads. When using a range of cells to fill a ListBox (ListFillRange described below) the Row immediately above the first Row is used as the ColumnHeads. This means if the ListFillRange was A2:D50 the range A1:D1 would be your ColumnHeads. If the ListFillRange was A1:D50 the ColumnHeads would be Excels Column headings, ie; A:D

This determines the width of each Column used in a ListBox. The setting must be a String. and each width separated with the PC's List separator, usually the ; (Semicolon). See the help text below:

This Property takes a range address or name as it's value. For example A1:D50 or MyRange (in the case of a named range) are valid entries.

This Property determines how your list will look. There are only two choices fmListStylePlain and fmListStyleOption The first is the default and has no real visual effect. The second will place small squares to the right of each Item in the ListBox which become checked when the user select the Item.

This Property is used to assist the user in looking for a particular Item in the list. It takes effect as the user starts to type.

If for example your list had the entries Aardvark,Absolute,Acorn,Addict etc and you set the MatchEntry to fmMatchEntryFirstLetter and the user typed A or a they would see Aardvark, if they then typed Ad or ad they would see Addict.

This determines whether the user can select more than one Item in the ListBox.

When the setting is fmMultiSelectMulti the user can select more than one item in the ListBox.

Sets and/or returns the Item that will appear in the top of the List. So if you had a list that contained 10 rows and set TopIndex to 5 the user would only be able to see the last 6 rows. The first row is 0 (zero).

Lets now look at the SpinButton and Scrollbar

This property sets the amount the user can move when the user clicks between the Scroll box and Scroll arrow.

This property determines the Maximum or Minimum Value the SpinButton or ScrollBar will increment to. The setting must be a Integer.

This will make the Control either vertical or horizontal. The default is for fmOrientationAuto which means Excel will position the Control based on the Controls dimensions.

Determines how movement will occur whenever a user spins or scrolls up or down. The default setting is 1. The Value must be an Integer.

Let's now look at the TextBox and some of it's Properties that have not been mentioned.

This Property takes a Boolean and determines what will be the base unit that is used to extend a selection. When set to TRUE the base unit is a word. When set to FALSE the base unit is a single character. If AutoWordSelect is set to TRUE and the user places the mouse insertion point into the TextBox in the middle of a word and then extends (drags) the selection, the entire word is selected. Doing this when AutoWordSelect is set to FALSE would mean only one character at a time would be selected.

This Property can be either enabled (fmDragBehaviorEnabled) or disabled (fmDragBehaviorDisabled). When enabled the user can drag-and-drop (cut or copy and paste). If the Property is disabled and the user drags within the TextBox any text is only highlighted.

This Property sets the method in which the text is selected when entering a TextBox. Its two settings are fmEnterFieldBehaviorSelectAll  (default) and fmEnterFieldBehaviorRecallSelection. when left set as fmEnterFieldBehaviorSelectAll the entire content of the TextBox is selected when the user enters the TextBox. If set to

fmEnterFieldBehaviorRecallSelection the selection is the same as the last time the Control was active.

This only applies when the user Tabs to the Control.

Takes a Boolean and determines whether selected Text still appears selected when the Control no longer has Focus. Setting this to TRUE (default) means Text is not highlighted unless the Control has Focus.

This Property sets the maximum number of characters that can be placed into a Control. The default is 0 (zero) which means the Control has no limit set and any number of characters can be entered.

*placeholder

A character that masks or hides another character for security reasons. For example, when a user types a password, an asterisk is displayed on the screen to take the place of each character typed.

So as you can see we are able to set the visual and actual effect that happens when the user does anything to a Control. The Properties can be used in different combinations to produce different effects. Most Properties can be set either at Design-time or Run-time. While some Properties can only be set at Design-time. The Font type is one of these. While we can set the Fonts attributes Bold, Italic, Underlined etc we cannot change the Font type, eg; Ariel to Times New Roman at Run-Time.

As mentioned earlier whenever a Control is placed on a Worksheet is becomes a Object of the Worsheet. This means it is part of the Worksheets Object collection. This basically means that if we want to access a Control that is on a Worksheet we must first go through the Worksheet Object as the Controls Parent is the Worksheet that has the Control embedded in it. Lets say we have a TextBox on a Worksheet who's CodeName is Sheet1 and we want to Select it. We would use:

Very easy indeed! But lets assume we have no idea of the name of the TextBox all we know is that a TextBox does exists on Sheet1. In this case we will need to Loop through all Objects on the Worksheet until we find one that is a TextBox. We can determine this by using the ProgId Property. But before we go and Loop through ALL Objects we can narrow our search down to the type of Object we are interested in. For ActiveX Controls the type is an OLEObject and the OLEObject is a member of the OLEObjects Collection. So we could use this method:

So as you can see we have used a For Each (with each referring to OLEObjects) to Loop through only OLEObjects that are on Sheet1. We then use the ProgId Property to check and see if the OLEObject is a TextBox or not. The list of identifiers for ActiveX Controls is shown below. The identifier of a ActiveX Control can also be seen in the Formula bar when the Control is Selected, eg; =EMBED("Forms.TextBox.1","")

So using the above list we can access any of the above mentioned ActiveX controls on any Worksheet.

As we have Events for the Workbook Object and Worksheet Object we also have Events for ActiveX Controls. We can see all the Events associated with a particular Control by viewing it's code and looking in the Procedure box. We can gain access to the code for a Control in two ways. Right click on the Control and select "View Code" or Double click the Control. For both methods we must be in Edit Mode.Whichever method we use, Excel will open up the Private Sub of the Worksheet. The Procedure written by default will be the default Procedure for the Control. This is usually the Change Event. For example double clicking a TextBox gives us:

We can then see all the Events this Control has by placing our mouse insertion point anywhere within the Procedure and clicking the drop down arrow in the Procedure box (top right of the Module). Most Controls have around 15 Events that are available to them. As with the Events for a Workbook or Worksheet any code within an Event Procedure for a Control will run whenever the Event occurs. The way in which we apply these Events is purely up to us.

For the rest of the lesson I have attached a Workbook that has Controls on it and some code placed within the Event Procedures. Browse through these at your leisure as the code is only very simple. We will look at the Events for Controls in the Excel UserForms course.