Workbook events using VBA in Microsoft Excel

by  About
       

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

  • Activate
  • AddinInstall
  • AddinUninstall
  • BeforeClose
  • BeforePrint
  • BeforeSave
  • Deactivate
  • NewSheet
  • Open
  • SheetActivate
  • SheetBeforeDoubleClick
  • SheetBeforeRightClick
  • SheetCalculate
  • SheetChange
  • SheetDeactivate
  • SheetSelectionChange
  • WindowActivate
  • WindowDeactivate
  • WindowResize

This example eventmacro will maximize Excel when a workbook is opened, the procedure must be written in the
workbook module ThisWorkbook:

Private Sub Workbook_Open()
    Application.WindowState = xlMaximized
End Sub

This example eventmacro will prevent the user from activation other worksheets than the first worksheet, the procedure must be written in the workbook module ThisWorkbook:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Worksheets(1).Activate
End Sub


2 thoughts on “Workbook events using VBA in Microsoft Excel

  1. Very helpful in that the tip stated that the visual basic code must be written in the “ThisWorkbook” module. I had been scouring the Excel VB help file to try and get the WorkBook_Open to work properly but it never stated that it must be written in a specific module. Thanks to your tip I was able to get the function working correctly.

Leave a Reply

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


7 − four =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>