Databases Reference
In-Depth Information
FIGURE 5.7
The RefEdit control on the component palette.
control in the Workbook RefEditErrors.xls. This Workbook was created and sent to Microsoft by the
author with the hope that they would fix the associated long-standing problems (bugs) within this control.
1.
The most nasty bug is the fact that the RefEdit control will not work with a nonmodal
form. If a form's modal property is set to False, and the form has a RefEdit control in
it, Excel will hang up and then crash. If a form's .ShowModal property is set to FALSE
at design time, the RefEdit control will not let go and return control of the macro to the
form. This is true even after a selection has been made on the form and/or the Enter
button has been pressed on the keyboard. This error can be demonstrated with the
Workbook “RefEditErrors.xls” included on the accompanying CD-ROM
(\Chapter 5
\Samples\RefEditErrors.xls) by choosing the option button “Hang and Crash Excel with
RefEdit Control Use.” If the “RefEdit Bugs Demo” button is pressed on the sheet with
foregoing option set, the only way to regain control of Excel is to shut Excel down using
Ctrl-Alt-Delete! This obviously greatly limits the utility of the control.
2.
The Events
AfterUpdate
and
BeforeUpdate
never fire when utilized by a RefEdit
Control. This is a debilitating bug for the following reasons. Immediately after a RefEdit
Control has its contents updated, it would be prudent to run a check on the contents to
ensure that the entered values point to where they should, or encompass a valid range
for which they are supposed to reference. It might also be advantageous to color the
current range pointed to in the RefEdit Control on the Worksheet by changing the color
of the font or background color in the cells referenced by the control. Because the
AfterUpdate
Event does not fire for the RefEdit control, creating such a validation
check is impossible. Conversely, immediately before updating the current contents of
the RefEdit Control, it might be necessary to make some changes to the last region it
pointed to prior to changing the contents of the RefEdit control. Perhaps the font in that
Range should have its color returned to normal. Because the
BeforeUpdate
Event
does not fire for the RefEdit control, creating such routines is not possible.
3.
Setting the .Font property utilizing either the
Enter
or
Exit
events with the RefEdit
control not only fails to work but causes the program to abnormally terminate as soon
as focus is set to any other object on the form other than the RefEdit control. Simply
uncomment out the marked VB code items in the Workbook “RefEditErrors.xls” to
observe this effect. Note that the code in the following subroutines works fine when
called from the Change Font Color button's click event!
Private Sub RefEdit_Enter()
'MsgBox "RefEdit Enter Event Occurred", vbOKOnly +
vbInformation, "Event Triggered"
