 |
|
|
|
Workbook Download
This is a zipped Excel Workbook to go with this lesson.
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.
END OF EXCEL HELPThis 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.