Back to Excel Newsletter
Archives
Back to Excel Newsletter
Archives
Go in the draw to win a free copy each month of;
Database Orchestra - Oracle Send blank email to enter.
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.
Ok, picking up where we left off last 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, or
Custom 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.
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 8
Application.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 0
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!