Excel

BeforePrint, ModelChange and NewChart Events for Workbook

In this tutorial, you’ll learn how to use the Workbook_BeforePrint, Workbook_ModelChange and Workbook_NewChart event procedures. Open a workbook and press Alt + F11 to open Visual Basic Editor (VBE). Then double-click the ThisWorkbook object in the Project Explorer tree to show its code module.

Workbook_BeforePrint

Syntax: Workbook_BeforePrint(Cancel)

The BeforePrint event occurs before the printing anything from the workbook. To listen this event, use the Workbook_BeforePrint event listener procedure which has one Cancel as Boolean argument. Setting Cancel argument to True cancel the print command so the workbook isn’t printed when the procedure is finished.

Workbook_BeforePrint Examples:

The following code shows how BeforePrint event can be used to set the footer information:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 ActiveSheet.PageSetup.RightFooter = "BrainBell.com"
End Sub

The following code shows how BeforePrint event can be used to cancel the print command sent to printer:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
 Cancel = True
End Sub

Workbook_ModelChange

Syntax: Workbook_ModelChange(Changes)

The ModelChnage event occurs after the data model is changed. To listen this event, use the Workbook_ModelChange event listener procedure to monitor the changes. This event procedure has one Changes as ModelChange argument which represents the changes made to the data-model.

Workbook_ModelChange Example:

Private Sub Workbook_ModelChange(Changes As ModelChange)
 MsgBox Changes.TableNamesChanged.Count
End Sub

Excel uses multiple ways to create a data model. For example, you can create a data mode by clicking the PowerPivot tab and then click Add to Data Model:

You also can create a data from a text file or database. To learn more about the data mode visit: Creating data model.

Workbook_NewChart

Syntax: Workbook_newChart(Ch)

The NewChart event occurs when a new chart is created in the workbook. To listen this event, use the Workbook_NewChart event listener procedure to control the chart. This event procedure has one Ch as Chart argument which represents the newly created chart.

Workbook_NewChart Example:

The following example displays a message, after adding a new chart, saying that a new chart has been created and also indicates the name of the chart and the type:

Private Sub Workbook_NewChart(ByVal Ch As Chart)
 MsgBox "The chart: " & Ch.name & ", type: " & Ch.ChartType & " has been created"
End Sub