Microsoft Office Tutorials and References
In Depth Information
The Worksheet_Change event is not triggered by a calculation change, such as a
formula returning a different value. Use the Worksheet_Calculate event to
capture the changes to values in cells that contain formulas.
The Worksheet_SelectionChange event occurs when a cell is selected. The following code
highlights the active cell with a yellow color every time a different cell is selected:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = 0
Target.Interior.Color = vbYellow
A word to the wise! This kind of code is fun and has its usefulness, but with each
change in cell selection, the Undo stack will be eliminated, essentially negating
the Undo feature.
The Worksheet_BeforeDoubleClick event is triggered by double-clicking a worksheet cell. The
Cancel argument is optional and halts the ability to go into Edit mode for that cell from a double-click.
In this example, if you double-click a cell in range A1:C8, and the cell already contains a number or is
empty, the numeric value of that cell increases by 1. All other cells in the worksheet are unaffected.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
If Intersect(Target, Range(“A1:C8”)) Is Nothing Then Exit Sub
If IsNumeric(Target.Value) = True Then
Cancel = True
Target.Value = Target.Value + 1
This event does not occur if you double-click the ill handle.