Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter November 2007

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

EXCEL TIPS AND TRICKS

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.
 

EXCEL VBA TIPS & TRICKS

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.

Download Example

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

Download Example

Until next month, keep Excelling!

Got any Questions? Free Excel Help

Complete Excel Training Course Special!
We don't teach Excel from a manual, we teach Excel from experience!
Free Help Forum, not just Excel!

Instant Download and Money Back Guarantee on Most Software

Add to Google Search Tips

Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!

Excel Data Manipulation and Analysis

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft

FREE Excel Help