Microsoft Office Tutorials and References
In Depth Information
Examining Worksheet Events
This example uses a Range object variable named MRange , which represents the worksheet
range that you’re interested in monitoring for changes. The procedure uses VBA’s Intersect
function to determine whether the Target range (passed to the procedure in its argument)
intersects with MRange . The Intersect function returns an object that consists of all the cells
that are contained in both of its arguments. If the Intersect function returns Nothing , the
ranges have no cells in common. The Not operator is used so the expression returns True if the
ranges do have at least one cell in common. Therefore, if the changed range has any cells in
common with the range named InputRange , a message box is displayed. Otherwise, the procedure
ends, and nothing happens.
Monitoring a range to make formulas bold
The following example monitors a worksheet and also makes formula entries bold and
nonformula entries not bold.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
For Each cell In Target
cell.Font.Bold = cell.HasFormula
Next cell
End Sub
Because the object passed to the Worksheet_Change procedure can consist of a multicell
range, the procedure loops through each cell in the Target range. If the cell has a formula, the
cell is made bold. Otherwise, the Bold property is set to False .
The procedure works, but it has a problem. What if the user deletes a row or column? In such a
case, the Target range consists of a huge number of cells. The For Each loop would take a
very long time to examine them all — and it wouldn’t find any formulas.
The modified procedure listed next solves this problem by changing the Target range to be the
intersection of the Target range and the worksheet’s used range. The check to ensure that
Target is Not Nothing handles the case in which an empty row or column outside of the used
range is deleted.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim cell As Range
Set Target = Intersect(Target, Target.Parent.UsedRange)
If Not Target Is Nothing Then
For Each cell In Target
cell.Font.Bold = cell.HasFormula
Next cell
End If
End Sub
 
Search JabSto ::




Custom Search