Worksheet events using VBA in Microsoft Excel

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

Users are saying about us...

  1. The Change event is not triggered when I select a value from a list cell which has validation. Only the Calculate event is trigrred. I am using MS excel 97. This seems to happen in excel 2000. Is there any way I could trigger the change event when the value in cell which has validation is changed in excel 97. Please reply ASAP.

  2. The BeforeRightClick event doesn't execute when I right-click on a cell border. Is there an event that does, or is there some other way to run code when a cell border is right-clicked?

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube