Intro to Worksheet Events and Selection_Change
double click worksheet in VBA editor
in the newly opened pane select Worksheet from the left dropdown
a Worksheet_SelectionChange event handler is added to the worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox Target.Address(0,0)
End Sub
Top
Index
can also view worksheet code by right-clicking the worksheet tab and using the context
menu
in the panes right dropdown the desired event handler can be selected
the event handler below is called when the worksheet becomes top of the Z-order
Private Sub Worksheet_Activate()
End Sub
Top
Index
Worksheet Deactivate Event
event is fired when worksheet is removed from top of Z-order
Private Sub Worksheet_Deactivate()
Sheets("Deactivate").Visible = false;
End Sub
Top
Index
BeforeDelete Event - for Worksheet Deletion
no way to cancel this event
seems pointless
Private Sub Worksheet_BeforeDelete()
...
End Sub
Top
Index
BeforeDoubleClick Event - Trigger a Macro when Double Clicking
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0,0) = "C3" Then
MsgBox "C3 double clicked"
' disable cell editing
Cancel = True
End Sub
End Sub
Top
Index
BeforeRightClick Event - Trigger a Macro when Right-Clicking
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address(0,0) = "C3" Then
MsgBox "C3 right clicked"
' prevent context menu from appearing
Cancel = True
End Sub
End Sub
Top
Index
F9 or Formulas|Calculate Sheet recalculates the entire worksheet
change any cell in a worksheet can cause the sheet to recalculate all cells
event handler below resizes columns to fit their content
Private Sub Worksheet_Calculate()
Columns("A:B").AutoFit
End Sub
Top
Index
How to Disable Events on the Workbook
Sub disableEvents()
Application.EnableEvents = False
End Sub
Sub enableEvents()
Application.EnableEvents = True
End Sub
Top
Index
change event fired when a cell's content is changed
Top
Index
Change Event with EnableEvents Toggle - Avoid Endless Loop
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If Target.Offset(0, -1) = "Bertha" Then
' prevent endless loop by turning off events
Application.EnableEvents = False
' change event won't be fired by this change
Target = 0.5
' turn events on
Application.EnableEvents = True
End If
End If
End Sub
Top
Index
Change Event Triggered with Custom Range Using Intersect
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("b2:b4")) Is Nothing Then
' the target is within the range
End If
End Sub
Top
Index
use Insert|Link to put a link in a cell
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
...
End Sub
Top
Index
disable right click and show message saying context menu will not be displayed
Top
Index
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:C")) Is Nothing Then
rowNumber = Target.Row
s = Range("A" & rowNumber).Value
If s <> "" Then
x = Range("C" & rowNumber).Value
y = Range("B" & rowNumber).Value
Range("D" & rowNumber) = x * y
End If
End If
End Sub
Top
Index