|  

» Workbook events using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions
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


Rate This Tip
12 34 5
Rating: 3.31     Views: 69846
Tip on WorkBook_Open function
Chad Holthaus  Posted on: 31-12-1969
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.
workbook_open
Pete  Posted on: 31-12-1969
a lifesaver - must be in the "thisworkbook" module!
Name
Comment Title
Comments