Page 286 - Excel Progamming Weekend Crash Course

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

Session 20 — Controls for User Forms 261

3. Add a RefEdit control to the form, leaving all of its properties at their default
values.
4. Add a CommandButton to the form. Change its Caption property to Format.
5. Double-click the CommandButton to open its Click event procedure in the editing
window.
6. Add the code from Listing 20-6 to this procedure.

Listing 20-6 Code in the RefEditDemo form

Private Sub CommandButton1_Click()
Dim s As String
Dim r As Range
s = RefEdit1.Value
‘ Check for no selection.
If s = “” Then
MsgBox “Please make a selection”
Else
‘ Format the range.
Set r = Range(s)
r.Font.Color = RGB(255, 0, 0)
‘ Display the range address.
MsgBox “You formatted the range “ & s
‘ Close the form.
Me.Hide
End If

End Sub
All the code for this program is in the user form itself, so all you need is a procedure to
display the form. Create a new procedure in a VBA module (you might call the procedure
TestRefEditDemo) and put the following code in the procedure:
Dim frm As New RefEditDemo
frm.Show
To test the program, follow these steps:

1. Run the procedure to display the form.
2. Click the RefEdit control to collapse the form and select a range in the worksheet.
It’s a good idea to select a range that contains data so you can see the effect of
the formatting. Figure 20-3 shows the worksheet with the collapsed user form and
a range selected.
3. Click the button on the collapsed form to return to the user form.
4. Click the Format button to format the range and display the message box.
5. Close the message box, which also closes the user form.