Workbook Events

The following workbook event procedures are available:

□ Private Sub Workbook_Activate()

□ Private Sub Workbook_AddinInstall()

□ Private Sub Workbook_AddinUninstall()

□ Private Sub Workbook_AfterXmlExport(ByVal Map As XmlMap, ByVal Url As String, ByVal Result As XlXmlExportResult)

□ Private Sub Workbook_AfterXmlImport(ByVal Map As XmlMap, ByVal IsRefreshAs Boolean, ByVal Result As XlXmlImportResult)

□ Private Sub Workbook_BeforeClose(Cancel As Boolean)

□ Private Sub Workbook_BeforePrint(Cancel As Boolean)

□ Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

□ Private Sub Workbook_BeforeXmlExport(ByVal Map As XmlMap, ByVal Url As String, Cancel As Boolean)

□ Private Sub Workbook_BeforeXmlImport(ByVal Map As XmlMap, ByVal Url As String, ByVal IsRefreshAs Boolean, Cancel As Boolean)

Private

Sub Workbook_

_Deactivate()

Private

Sub Workbook_

_NewSheet(ByVal ShAs Object)

Private

Sub Workbook_

_Open()

Private

Sub Workbook_

_PivotTableCloseConnection(ByVal Target As PivotTable)

Private

Sub Workbook_

_PivotTableOpenConnection(ByVal Target As PivotTable)

Private String,

Sub Workbook_RowsetComplete(ByVal Description As String, ByVal Sheet As ByVal Success As Boolean)

Private

Sub Workbook_

_SheetActivate(ByVal ShAs Object)

Private

Sub Workbook_

_SheetBeforeDoubleClick(ByVal ShAs Object, ByVal Target As

Range, Cancel As Boolean)

Range, Cancel As Boolean)

□ Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

□ Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

□ Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

□ Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

□ Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

□ Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, ByVal Target As PivotTable)

Private

Sub Workbook_

_SheetSelectionChange(ByVal ShAs Object, ByVal Target As

Range)

Private

Sub Workbook_

_Sync(ByVal SyncEventType As Office.MsoSyncEventType)

Private

Sub Workbook_

_WindowActivate(ByVal Wn As Window)

Private

Sub Workbook_

_WindowDeactivate(ByVal Wn As Window)

Private

Sub Workbook_

_WindowResize(ByVal Wn As Window)

Some of the workbook event procedures are the same as the worksheet and chart event procedures. The difference is that when you create these procedures (such as the Change event procedure) in a worksheet or chart, it applies to only that sheet. When you create a workbook event procedure (such as the SheetChange event procedure), it applies to all the sheets in the workbook.

One of the most commonly used workbook event procedures is the Open event procedure. This is used to initialize the workbook when it opens. You can use it to set the calculation mode, establish screen settings, alter the Ribbon, or enter data into combo boxes or list boxes in the worksheets.

Similarly, the Workbook_BeforeClose event procedure can be used to tidy up when the workbook is closed. It can restore screen and option settings, for example. It can also be used to prevent a workbook's closure by setting Cancel to True. The following event procedure will only allow the workbook to close if the figure in the cell named FinalProfit is between 500 and 600:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim dProfit As Double

dProfit = ThisWorkbook.Worksheets(1).Range("FinalProfit";

i.Value

If dProfit < 500 Or dProfit > 600 Then

MsgBox "Profit must be in the range 500 to 600"

Cancel = True

End If

End Sub

Note that if you assign True to Cancel in the workbook BeforeClose event procedure, you also prevent Excel from closing.

0 0

Post a comment