Microsoft Office Tutorials and References
In Depth Information
Workbook_SheetPivotTableUpdate Event
Workbook_sheetpivotTableupdate Event
The SheetPivotTableUpdate event monitors all worksheets in the workbook that hold pivot tables.
This event code example informs you in real time which worksheet(s) with pivot tables have been
updated. Note that the Message Box identifies the worksheet name(s) with the Sh.Name expression,
which is done by referring to the Object argument of Sh from the event’s set of parameters.
Private Sub Workbook_SheetPivotTableUpdate(ByVal Sh As Object, _
ByVal Target As PivotTable)
MsgBox “The pivot table on sheet “ & Sh.Name & “ was updated.”, , “FYI”
End Sub
Workbook_newsheet Event
The Workbook_NewSheet event is triggered when a new sheet is added to the workbook. To see this
event in action, suppose you do not want to formally protect the workbook, but you want to
disallow the addition of any new worksheets. This event procedure promptly deletes a new sheet as soon
as it is added, with a message box informing the user that adding new sheets is not permitted:
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim asn As String
asn = ActiveSheet.Name
Application.EnableEvents = False
Application.DisplayAlerts = False
MsgBox “New sheets are not allowed to be added.”, vbCritical, “FYI”
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub
Workbook_Beforeprint Event
The Workbook_BeforePrint event is triggered before a user attempts to print any portion of the
workbook. You can cancel the print job by setting the Cancel parameter to True. If you want to
ensure that anything printed from that workbook will have the workbook’s full name in the footer
of every printed page, the following procedure accomplishes that:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
sht.PageSetup.CenterFooter = ThisWorkbook.FullName
Next sht
End Sub
When you test the Workbook_BeforePrint procedure, you can use the
PrintPreview method instead of the PrintOut method, which can save
you costs in paper and printer toner cartridges.
Search JabSto ::

Custom Search