Databases Reference
In-Depth Information
FIGURE 5.8
A RefEdit control in a form.
'If RefEdit.Text <> "" Then Range(RefEdit.Text).Font.Color
= vbBlue
End Sub
Private Sub RefEdit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'MsgBox "RefEdit Exit Event Occurred", vbOKOnly +
vbInformation, "Event Triggered"
'If RefEdit.Text <> "" Then Range(RefEdit.Text).Font.Color
= vbRed
End Sub
With all of these associated problems with the RefEdit control, the reader may wonder why it
is utilized in so many example applications or why it is even utilized at all. The answer, quite
simply, is that it is the only game in town. A process of any degree of sophistication will require
the user to select ranges within Worksheets, and the RefEdit control is the only mechanism available
to accomplish this task. Therefore, building an application that is even remotely complex will
require the developer to utilize the RefEdit Control and work around its associated problems. When
a RefEdit control is placed in a form, it looks like the image shown in Figure 5.8.
When the RefEdit control in a form becomes active, the form will disappear, and a small
rectangular box will appear that displays the region selected on the sheet. In addition, the region
currently selected by the RefEdit control will have dashed lines surrounding it on the active sheet,
indicating the current selection the user is making. A small box will also appear to the bottom right
of the range selected, indicating the number of rows and columns currently selected in the RefEdit
control. Figure 5.9 shows the RefEdit control when used in the Workbook RefEditErrors.xls.
Once a range has been selected in a RefEdit control, the logical question becomes how can the Range
selected within the control be utilized within Excel VBA Code? There are a number of ways to utilize
the Range returned by a RefEdit control, and those that lend themselves to repeated use will be discussed.
A sample application has been created to show the reader how to utilize the RefEdit control.
It can be run by selecting
A
DA->Chapter
5
->Ref
E
dit Control Use from the menu, which will
cause the GUI shown in Figure 5.10 to appear. This application utilizes the range selected by the
user with the RefEdit control in the GUI and then returns relevant information about the selected
range when the “Range Info” button is pressed. The mechanisms that enable this example to work
will now be discussed.
The first parameter returned tells the user if the range selected in the RefEdit control encom-
passes a single cell or a (group) range of cells. This information is determined with the
Single-
CellInRange
function.
Function SingleCellInRange(RefEditTxt) As Boolean
'If a ":" is in the Range then More than one cell is in Range!
If InStr(RefEditTxt,
":")
<> 0 Then
SingleCellInRange = False
Else
SingleCellInRange = True
End If
End Function
