Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter December 2007

Add to GoogleSearch Tips Excel Data Manipulation and Analysis

Go in the draw to win a free copy each month of;

  1. Database Orchestra - Oracle Send blank email to enter.

  2. Database Orchestra - MYSQL Send blank email to enter

Winner notified by email on about the 1st of each month starting Jan 1st 2008. ALL winners will be removed from the draw after winning.

EXCEL TIPS AND TRICKS

Ok, picking up where we left offlast month, let's look at some of the remaining Forms Controls.

OPTION BUTTONS
These controls are used when a user can make one choice from multiple choices, or a group of choices. With that in mind, Option Buttons should only be used in pairs at minimum. If you only have 1, the user will not be able to deselect it.

By default, when you add your Option buttons, they will be associated with each other. Meaning you can only check one at a time. You specify which one will be checked by default, or you make them all unchecked. You do this by right clicking on them and choosing Format Control and then use the Control tab. Note that the Mixed option remains grayed out, unlike Check Boxes.

CELL LINK
The Cell Link option is used to link any single cell to the Option buttons. As soon as you set this, all others, by default, will automatically link to the same cell. When an Option button is checked, the number of the Option button is returned to the Cell Link. These numbers are determined by the order in which you add Option buttons to a Worksheet. So, if you added 6 Option buttons to a Worksheet and linked 1 to cell A1, the other 5 would also be linked to A1.  If you check the 1st Option button, the number 1 will appear in A1. If you checked the 2nd Option button, number 1 will uncheck and the number 2 will appear in A1 and so on...

CAPTION
You can change the caption of your Option button by right clicking on it and choosing Edit Text.

NAME
You can change the name of your Option buttons by selecting the Option button and clicking in the Name Box (left of the formula bar) and entering any name.

GROUPING
To group your Option buttons into groups you use the Group box control. All Option buttons inside the same Group box will be linked to each other making it only possible to choose 1 from the group. Also, each 'group' will return a number between 1 and the number of Option buttons in that group to the Cell link.

CELL LINK NUMBER
As mentioned, a number is returned to the Cell link of the group. This number can then be used to return some meaningful text, or value to any other cell. With that in mind, you may wish to use a Cell Link that is out-of-sight, orCustom Format it like ;;;

You would often use a Lookup function to return some meaningful information. Here is a quick and dirty way to use the Choose Function.

=IF(A1="","",CHOOSE(A1,"Option 1","Option 2","Option 3","Option 4"))


LIST BOX
This control allows you to display a list of values to users and they can choose one or more values from the list.

Right click on the List Box and choose Format Control and then the Control tab. On here you will see;

1) Input Range. A single column range of cells.

2) Cell Link. A cell to display the index number of the List box item selected. Note: Only works if Selection Type is Single.

3) Selection Type
 a) Single. User can only select 1 item in the list.
 b) Multi. User can select 1 or more items in the list.
 3) Extend. User can select 1 or more items in the list by dragging with their left Mouse button held down.


To make use of the Cell Link, you would again use a Lookup function. Assume your Input Range is A1:A10 and your Cell Link is C1. In any cell you could use;

=INDEX(A1:A10,C1,1)

To return the chosen item from the List Box.

COMBO BOX
Exactly the same as the List Box but only allows a single selection. You can also set the number of items a user sees when selecting the drop arrow via the Drop Down Lines on the Control page tab of the Format Control dialog.

Next month we will complete this series on Forms Controls by looking at the last 2. Scroll Bar and Spinner.

EXCEL VBA TIPS & TRICKS

Use Array Elements To Fill a Range With Headings

You can use the code below to fill a user chosen range (including headings) with array elements. It makes use of the Type 8Application.InputBox

The best way to see what the code does is to run it :)

Sub ArrayToTable()    Dim vArray(), vArrayHeadings()    Dim rTable As Range    Dim rCell As Range    Dim lArrayElmnt As Long    Dim lHeads As Long, lRows As Long    Dim lreply As Long, lDataCells As Long          'Fill   arrays    vArray = Array(1, 2, 3, 4, 5, 6, 7)    vArrayHeadings = Array("Head1", "Head2", "Head3", "Head4")         On Error Resume Next    Set rTable = Cells(1, 1)    Set rTable = Application.InputBox(Prompt:="Select Table Range", Type:=8)    If rTable Is Nothing Or rTable.Address = "$A$1" Then Exit Sub 'cancel or non valid range         lHeads = UBound(vArrayHeadings) + 1    lDataCells = UBound(vArray)    lRows = lDataCells / lHeads         If lHeads <> rTable.Columns.Count Then        lreply = MsgBox("Selection Range Must Have " & _        lHeads & " Columns. Try Again", vbQuestion + vbCritical + vbOKCancel)                 If lreply = vbCancel Then            Exit Sub        Else            Run "ArrayToTable"        End If    ElseIf rTable.Rows.Count <> lRows + 1 Then        lreply = MsgBox("Table Range (Including  Headings) Must Be " & _        lHeads & " Columns Wide By " & lRows + 1 & " Rows High." _        & " Try Again", vbQuestion + vbOKCancel)                 If lreply = vbCancel Then            Exit Sub        Else            Run "ArrayToTable"        End If    End If              With rTable            With .Range(Cells(1, 1), Cells(1, UBound(vArrayHeadings) + 1))                 .Value = vArrayHeadings                 .Font.Bold = True            End With        Set rTable = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)    End With         For Each rCell In rTable        rCell = vArray(lArrayElmnt)        lArrayElmnt = lArrayElmnt + 1        If lArrayElmnt = lDataCells Then Exit Sub    Next rCell         On Error GoTo 0End Sub

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