ERLANDSEN DATA CONSULTING Excel & VBA Tips   Informasjon på norsk / Information in Norwegian

These pages are no longer updated and are only available for archive purposes.

Click here to visit the pages with updated information.

Worksheet events

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

 

Document last updated 1999-08-21 12:47:53      Printerfriendly version

User comments:
Ole P. from Norway wrote (2005-12-15 12:42:53 CET):
Re: BeforeRightClick event
The worksheet object has no event macro for catching key-presses.
The BeforeRightClick-event macro will only catch right-mouse-clicks, not keyboard keys.
Benuar C. SF. from San Ducan wrote (2005-12-15 04:45:58 CET):
BeforeRightClick event
It is useless for the BeforeRightClick event as we still can activate the shortcut menu by pressing the shortcut button from the keyboard (next to the Windows button).
Ole P. from Norway wrote (2004-08-13 09:33:14 CET):
Re: Question
The Worksheet_Change event does not occur when the cell is recalculated, only when the cell content/formula is changed.
You can however use the Worksheet_Calculate event to capture changes in cell values.
Truman from Chicago wrote (2004-08-13 00:50:02 CET):
Question
Hello,

I've got a random number generating function in a cell, how come the worksheet_change function does not capture the "change" in the cell, everytime
the random number function generates a new number ? how do I get around it ? Mucho thank you.

Truman
marcus gruendel from berlin, germany wrote (2004-06-04 17:49:37 CET):
Thanks a lot!!
Aloah,

evry time I have a Excel problem to solve, I browse thru your website. And almost always I find a nice and well working solution. Thank you for your wonderful site!

 

Erlandsen Data Consulting     http://www.erlandsendata.no/   
Excel & VBA Tips   Copyright ©1999-2017    Ole P. Erlandsen   All rights reserved
E-mail Contact Address