Excel Controls From the Forms Toolbar and Controls from the Control Toolbox Toolbar. Which Type to Use?
Got any Excel/VBA Questions? Free Excel Help
Microsoft Excel Offers Two Types of Controls
- Controls From The Forms Toolbar
- Controls From The Control Toolbox Toolbar
Controls from the Forms Toolbar
A dilemma often arises when working in Excel as to which Control type to use. Let's first look at the Controls from the Forms Toolbar. These Controls are only available to use on an Excel Worksheet. They are NOT ActiveX Controls like the Controls from the Control Toolbox Toolbar are. The bottom line to this is that they are not as flexible in their use as the ActiveX controls are. However, this is generally not a problem for most Excel users.
The Controls from the Forms Toolbar can be found via View>Toolbars>Forms. The chosen control can be placed on a Worksheet simply by clicking it on the Forms Toolbar then clicking any cell on the Worksheet. You will then be prompted by Excel to "Assign a Macro" to the Control. This can be done immediately, at a later stage, or not at all. If the Control is a Command Button you will most likely be assigning a macro at some stage. After adding your Control to your Worksheet you will need to right click on it and select "Format Control" and then the "Control" page tab. Note that Labels and Command Buttons do not have a "Control" page tab.
Depending on the Control type that you select you will have different options available to you. The general idea behind all these Controls (except Labels and Command Buttons) is that you link the Control to a cell on the Worksheet (Cell Link). After doing this a value will be returned to a cell whenever you use the Control. In the case of an ComboBox, List box, Scroll bar and Spinner this value will be a whole number. For example, if you link a ComboBox to cell A1 and reference a list in B2:B11 for the "Input range", each time you make a selection from the ComboBox the number that is retuned will correspond to the selections position in the list, i.e. you select the second item, then the number returned will be 2. If the Control type is a Option Button or Check Box the linked cell will return True or False when the control is used.
What you can now do is use the result from the Cell Link in a formula to obtain your desired result. For example, if you were using a ComboBox linked to cell A1, the Input Range for it was in B1:B11, and also in range C2:G11 you have data that corresponds to the the Input Range you could use the VLOOKUP Function to return a corresponding entry, say in Column D, like below;
=VLOOKUP($A$1,$B$2:$G$11,3,False)
OR if we have named the ranges (good practice)
=VLOOKUP(ComboLink,DataTable,3,False)
As mentioned above, this is often enough for the majority of Excel users. Take the time to experiment with these controls and you will be amazed (with some lateral thinking) just how much you can do in Excel with a changing whole number and/or the Boolean value True and False.
Controls From the Control Toolbox Toolbar
These controls, which are ActiveX Controls, offer far more flexibility than their counterparts from the Forms toolbar. These Controls are available in both the Excel interface and the Visual Basic Editor. The reason they are very flexible is that we can trap their Events. The Event could be a simple click, a double click, change (e.g. new selection from a ComboBox), a user leaving the control and shifting focus to another control or back to the Excel interface and many more.
ActiveX Controls also have a long list of Properties (Font, Caption, Name, Linked Cell, Height and much more). The Properties are dependent on the type of Control, however Properties such as the Name are common across all controls as are many other Properties.
It is rare that an ActiveX Control is used without also using some VBA code that is run as one of it's Events take place. If you are not familiar with VBA you should probably opt for a Control from the Forms Toolbar. When an ActiveX Control is added to a Worksheet it is embedded and becomes an Object member of the Worksheet, which is also an Object. This will also automatically put us into "Design Mode" which in turns allows us to work with the Control without any Events firing. To trap any one of the Events of the Control, the Event Procedure must be placed in the Private Module of the Worksheet Object. This is done by double clicking the Control (while in design mode). This takes you straight to the Private Module and defaults to a particular Event Procedure, often the Click or Change Event. To specify another available Event Procedure, ensure you are somewhere in an existing Event Procedure, then select one from the "Procedure" Combobox, which is located at the top right of the Private Module.
*ActiveX controls are usually fully programmable objects that can be used by developers to create highly customised applications within the shell of the original application.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.
Gallery
