Page 285 - Excel Progamming Weekend Crash Course

n540629 ch20.qxd 9/2/03 9:35 AM Page 260

260 Saturday Evening

Listing 20-5 A procedure to test the OptionButtonDemo form

Public Sub TestOptionButtonDemo()

Dim frm As New OptionButtonDemo
Dim s As String

frm.Show
‘ Determine which option button is selected.
If frm.optBlue.Value = True Then
s = “Blue”
ElseIf frm.optRed.Value = True Then
s = “Red”
ElseIf frm.optGreen.Value = True Then
s = “Green”
End If
MsgBox “You selected “ & s

End Sub
Run the TestOptionButtonDemo program, and the form that you designed is displayed.
Select a color, noting how you can select only one at a time. When you click OK, the pro-
gram displays your choice in a Message Box.

The RefEdit Control
The RefEdit control enables the user to select a range of cells in a worksheet. After the range
has been selected, the control returns the address of the range as a string containing the
name of the sheet and absolute references to the cells. The RefEdit control can be used only
on forms that are shown modally (that is, they have the ShowModal property set to True).
The RefEdit control displays as a text entry box adjacent to a button. When the user
clicks the button, the user form collapses to a small size. The user can then use the mouse
to drag over a range in the worksheet. When the desired range has been selected, the user
clicks the button displayed on the collapsed user form. The user form expands to its original
size, with the sheet and cell address of the selected range displayed in the text entry box.
This text is available in code via the control’s Value property. If the user has not selected a
range, this property returns a blank string.
To see the RefEdit control in action, create and build this demonstration program. The
program displays a form with a RefEdit control and a button labeled Format. The user clicks
the RefEdit control and then selects a range in a worksheet. Upon returning to the user
form, clicking the Format button changes the font in the range to red and displays a mes-
sage box with the range address in it. Finally, the form is closed.
To create the user form, follow these steps:

1. Select Insert ➪ User Form to add a new user form to the project.
2. Change the form’s Name property to RefEditDemo and its Caption property to
RefEdit Demo.