Recent Addins ADO Application Arrays Charts Commandbars Count Dates Dialogs Events File access Files & Folders Functions Games Help Import & Export Lookup Off topic Other Outlook PivotTables Power Query Printing Problem solving Read me Searching Sorting Sound Strings Sum Time VBA programming VBE Workbooks Worksheets

Worksheet events

 1999-08-21    Events    0    193

Events for the worksheet object occurs when a worksheet is activated or the user changes the content of a cell. Events in worksheets are activated by default, but can be deactivated by a macro. To display the event procedures for a worksheet you use the Visual Basic Editor. Select the desired project in the Project-window and activate the worksheet object you want by doubleclicking it. Any event procedures in the worksheet will now be displayed in the Code-window on the right side of the screen. You can create a new event procedure by selecting Worksheet in the Object dropdown, and then select an event in the Procedure dropdown.
Macros can be attached to the following events in a worksheet:

Activate
BeforeDoubleClick
BeforeRightClick
Calculate
Change
Deactivate
SelectionChange
This example eventmacro will prevent the user from accessing the shortcut menus in a worksheet, the procedure must be written in the worksheets own module sheet, e.g. Sheet1: 

Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Cancel = True
    MsgBox "Shortcut menus are disabled in this worksheet!"
End Sub
This example eventmacro will prevent the user from selecting cells in a specific range in a worksheet, the procedure must be written in the worksheets own module sheet, e.g. Sheet1: 

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    If Not Application.Intersect(Target, Range("A1:A100")) Is Nothing Then
        Cells(ActiveCell.Row, 2).Select
        MsgBox "You can't select cells in A1:A100!"
    End If
End Sub