Databases Reference
In-Depth Information
Private Sub Button_ChangeColor_Click()
If RefEdit.Text = "" Then
MsgBox "You Must Choose a Range!", vbOKOnly + vbExclamation,
"No Range Selected!"
Exit Sub
End If
If ColorComboBox.Text = "" Then
MsgBox "You Must Choose a Highlighting Color", vbOKOnly +
vbExclamation, "Highlighting Color Not Specified!"
Exit Sub
End If
Range(RefEdit.Text).Font.Color =
Colors(ColorComboBox.ListIndex + 1, 2)
If CheckBox_Bold.Value = True Then
Range(RefEdit.Text).Font.Bold = True
End Sub
The “Clear Formats” button allows the user to erase all the formatting on the active Worksheet.
This can very easily be accomplished with a single line of code.
Cells.ClearFormats
Recall that the RefEdit control cannot be utilized with nonmodal forms. This project is simple in
nature in that it assumes that any formatting applied utilizing a RefEdit control was done to the current
active Worksheet because a modal form will not allow the user to activate a new Workbook. Although
a modal form will allow a user to jump around to different Worksheets, often when this is done while
a RefEdit control is active, Excel will crash, and the user will see a message box like that shown in
Figure 5.12. It is best to confine use of the RefEdit control to select ranges on the current active sheet.
Notice that a sample of the color the font is to be changed to is displayed right next to the
combo box that selects the color of the font. This is accomplished quite easily with a label control
and a single line of code that is executed whenever the contents of the Color Combo Box is changed.
Private Sub ColorComboBox_Change()
Color_Label.ForeColor = Colors(ColorComboBox.ListIndex + 1, 2)
End Sub
The second sample application demonstrates an equally important method of calling attention to
Worksheet data, and that is highlighting the background of a cell or a particular group of cells. A really
good color for this purpose is yellow because it gives the impression that someone took a highlighter
and just colored in the background of the cells in the range of interest. This application was constructed
utilizing the form reuse methods described earlier in this chapter. Because this application has nearly
the same functionality as the previous application, there was no sense in creating a form from scratch.
FIGURE 5.12
Possible result of changing Worksheets with an active RefEdit control.
