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 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.
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 SubUntil 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