Back to Excel Newsletter Archives Back to Excel Newsletter Archives
This month I thought we would look at using the Controls available from the Forms toolbar.
These Controls require no VBA code to drive them, unlike the ActiveX Controls from the Control Toolbox toolbar.
Firstly you will need to show the Forms toolbar. Go to View>Toolbars>Forms. Going from left-to-right the available controls are;
1) Label
2) Group Box
3) Button
4) Check Box
5) Option Button
6) List Box
7) Combo Box
8) Scroll Bar
9) Spinner
ADDING CONTROLS
All controls are added to a Worksheet and sized in the same manner. That is, click the needed control from the Forms toolbar then click where the control should reside on the Worksheet. Then simply resize the control using the white circular size handles. The one control that acts differently when added to a Worksheet is the Button control. As soon as you add this control Excel will prompt you to assign a Macro. This can be done at the time, later or not at all.
Although as you will see, the Button control is not much use with having a Macro assigned.
NAMING CONTROLS
To rename a control you simply select it and type a name in the Name Box, left of the formula
bar and push Enter. This is the same Name Box you would use toname ranges, embeddedcharts, auto shapes and shapes from the Drawing toolbar. The name of a control is NOT what you see within the control itself, that is the caption. The control name can only be seen in the Name Box when the control is selected.
ASSIGNING MACROS
All controls can have macros assigned to them. However, the Button control is most likely control to have a Macro assigned as is pretty much useless without one. To assign a Macro, select the control and right click on use....................wait for it.........Assign Macro!
FORMAT CONTROLS
This term is a bit misleading as some Forms controls cannot be formatted for color, font type etc. To access Format Control, right click on the control and choose Format Control. Not all controls have the same tabs on the Format Control dialog. The Button Control has the most tabs and is the most flexible in color, font etc formatting. The most common tabs are; Size | Protection | Properties | Web | Control. As we go through each control individually we will look at these and more in detail.
Let's now see how these controls can be used and for what situations they are best suited for. We will only look at the 1st 4 controls in detail this Month and go into detail on the remaining 5 next Month.
LABEL
As the name suggests, this control is used to label other controls or data. Other than change the caption, there is nothing unique that can be done to a Label control.
GROUP BOX
A Group Box is best suited to grouping Option Button controls, which we will look at next Month. However, they can be used to group any controls, including ActiveX controls. These too have a caption that can be changed to suit.
BUTTON
These, as mentioned above, are only good for assigning Macros but are the most flexible in formatting.
CHECK BOX
These are perhaps the most frequently used control. They are used to return TRUE (1) / FALSE (zero) to their Cell Link (more on that soon). In case you are wondering, TRUE has a numeric value of 1 while FALSE has a numeric value of zero. You would be amazed at just how much can be done with 1 and zero with a little imagination. In fact Computers are based on these 2 numbers.
They should NOT be used in numbers when only 1 should be checked. For that, you should use Option Buttons.
Right click on a Check Box and choose Format Control. Then click the Control tab. The 3 options you should see for the Value are; Unchecked (FALSE/ZERO), Checked (TRUE/1) and Mixed (N/A).
Below this is the Cell link. This can be a cell address or a named range (both should be a
single cell). The Cell link for a control can also be set or edited by selecting the control and typing = in the Formula bar then click any cell with your Mouse and push Enter.
Below this is the option to have the control formatted for 3D.
Once you have linked your Check Box to a cell you can use the box to change its state to TRUE/FALSE. The cell you have used for the Cell link will reflect its state. If you would rather see 1/0 in place of TRUE/FALSE, set the Cell link to an out-of-view cell and reference it like below;
=0+CheckBoxCellLink
Where "CheckBoxCellLink" is your named cell or cell address. The "0" can also be any number you choose. Just be aware if do so and also start with an initial state of Mixed (N/A), the cell will return #N/A until the Check Box is checked (TRUE) or unchecked (FALSE). To overcome this, use;
=IF(ISNA(CheckBoxCellLink),"",0+CheckBoxCellLink)
A NIFTY USE FOR TRUE/FALSE
As I mentioned above, 1 and zero (TRUE and FALSE) can be used in a multitude of different ways, all that is needed is some imaginative thinking. For example,Data Validation,Conditional Formatting and AdvancedFilter etc all rely on TRUE/FALSE. We can make use of this simple logic to do things that most only think is possible via VBA code.
To give you a start on this line of thinking, see thisdownload example.
MOVE LISTBOX ITEMS UP AND DOWN
Here is a relatively easy way to have an UP/DOWN button on a UserForm so users can change the order of the items in a ListBox.
Add 2 CommandButtons to your UserForm and Name them MoveUp and MoveDown. Then, use the RowSource Property of the Multi Column ListBox to fill the ListBox with a Named Range. Ensure this named range has headings BUT are NOT included in the range name definition. Set the ColumnHeads Property of the ListBox to TRUE and the ColumnCount to as many columns as the named range has Columns. Set the ColumnWidths as needed.
Now use the 2 Procedures below in their respective Down/Up Command Buttons
Private Sub MoveDown_Click() Dim lCurrentListIndex As Long Dim strRowSource As String Dim strAddress As String Dim strSheetName As String With ListBox1 If .ListIndex < 0 Or .ListIndex = .ListCount - 1 Then Exit Sub lCurrentListIndex = .ListIndex + 1 strRowSource = .RowSource strAddress = Range(strRowSource).Address strSheetName = Range(strRowSource).Parent.Name .RowSource = vbNullString With Range(strRowSource) .Rows(lCurrentListIndex).Cut .Rows(lCurrentListIndex + 2).Insert Shift:=xlDown End With Sheets(strSheetName).Range(strAddress).Name = strRowSource .RowSource = strRowSource .Selected(lCurrentListIndex) = True End With End Sub Private Sub MoveUp_Click() Dim lCurrentListIndex As Long Dim strRowSource As String Dim strAddress As String Dim strSheetName As String With ListBox1 If .ListIndex < 1 Then Exit Sub lCurrentListIndex = .ListIndex + 1 strRowSource = .RowSource strAddress = Range(strRowSource).Address strSheetName = Range(strRowSource).Parent.Name .RowSource = vbNullString With Range(strRowSource) .Rows(lCurrentListIndex).Cut .Rows(lCurrentListIndex - 1).Insert Shift:=xlDown End With Sheets(strSheetName).Range(strAddress).Name = strRowSource .RowSource = strRowSource .Selected(lCurrentListIndex - 2) = True End With End Sub
Until next month, keep Excelling!
Got any Questions? Free Excel Help
Complete Excel Training Course Special! 
Free Help Forum, not just Excel! Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
FREE Excel Help