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 to
name ranges, embedded
charts, 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 this
download 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
FREE Download!