Microsoft Office Tutorials and References
In Depth Information
Examples of Common Worksheet Events
In the preceding syntax example, the EnableEvents property of the Application
object was temporarily set to False with the statement
Application.EnableEvents = False
and then set back to True at the end of the macro with the statement
Application.EnableEvents = True
Keep in mind that the Application object covers all of Excel. For example, while a
macro is running with the EnableEvents property of the Application object set to
False, EnableEvents is disabled for all open workbooks in that instance of Excel,
not just for the workbook where the VBA code is being executed. Whatever
properties of the Application object you temporarily change, remember to reset those
properties back to their original settings before you exit your macro or procedure.
ExAMplEs of coMMon WorksHEET EVEnTs
At the worksheet level, Excel version 2003 has nine events, and five more than that (associated with
pivot tables) for a total of 14 in versions 2007 and 2010. The most commonly used worksheet events
are the nine that are common to all versions of Excel from 2000 to 2010:
Worksheet_Change
Worksheet_SelectionChange
Worksheet_BeforeDoubleClick
Worksheet_BeforeRightClick
Worksheet_FollowHyperlink
Worksheet_Activate
Worksheet_Deactivate
Worksheet_Calculate
Worksheet_PivotTableUpdate
Worksheet_change Event
The Worksheet_Change event occurs when cells on the worksheet are changed by the user or by an
external link, such as a new value being entered into a cell, or the cell’s value being deleted. The
following example places the current date in column C next to a changed cell in column B:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 2 Then Exit Sub
Target.Offset(0, 1).Value = Format(VBA.Date, “MM/DD/YYYY”)
End Sub
 
Search JabSto ::




Custom Search