ÂŽ
Using the NewChart Event in Excel 2010 Let expert author and Excel guru John Walkenbach guide you through using a new event that is exposed in Excel 2010—the NewChart event. This event occurs when a new chart is added to the workbook, and you can learn how to take advantage of the event by following these simple steps.
About events You can write VBA code (called event-handler procedures) that executes when a particular event occurs. A commonly used event is opening a workbook. For example, to execute a procedure called Initialize when the workbook is opened, insert this procedure in the code module for ThisWorkbook: Private Sub Workbook_Open() Call Initialize End Sub Excel exposes many events, and VBA programmers can take advantage of these events to perform tasks when a particular event occurs. It's important to understand that every event-handler procedure has a predetermined name. In addition, the event-handler procedures must be located in the correct code module. For example, workbook-level event-handler procedures must be located in the ThisWorkbook module. Worksheet-level procedures must be in the module for the particular sheet (for example, the Sheet1 module).
Writing NewChart event-handler procedures Before Excel 2010, it was not possible to write VBA code that executed automatically when a chart was added to the workbook. But, thanks to the new NewChart workbook event, such code is possible. Code that is triggered by adding a new chart must be placed in the ThisWorkbook code module, and the eventprocedure name must be Workbook_NewChart. The NewChart event can help ensure that all charts are created with a consistent style and size. Here's an example of a NewChart event-handler procedures. When a new chart is added, the code makes the chart perfectly square by setting the width and height to 300, and it also adds a title to the chart. Private Sub Workbook_NewChart(ByVal Ch As Chart) With Ch.Parent .Height = 300 .Width = 300 End With ActiveChart.SetElement (msoElementChartTitleAboveChart) End Sub
978-0-470-47535-5
As with all events, you need to understand all situations in which the event is fired. For example, I discovered that the NewChart event occurs if you move an embedded chart to a chart sheet. In such a case, the preceding code ends with an error because the parent object of a chart on a chart sheet is a workbook. When a chart is created on a worksheet, the parent of the Chart object is a ChartObject object. Here's a corrected version that checks the TypeName property of the parent of the newly created chart: Private Sub Workbook_NewChart(ByVal Ch As Chart) If TypeName(Ch.Parent) = "ChartObject" Then With Ch.Parent .Height = 300 .Width = 300 End With ActiveChart.SetElement (msoElementChartTitleAboveChart) End If End Sub
Other books in the Mr. Spreadsheet’s Bookshelf series: If you enjoyed this tip and would like to learn more about using Excel, check out Excel 2010 Power Programming with VBA and these other titles from John Walkenbach’s Mr. Spreadsheet’s Bookshelf series.
978-0-470-47537-9
978-0-470-62012-0
978-0-470-47536-2
About the Author: John Walkenbach has written hundreds of articles and created the awardwinning Power Utility Pak. His 50-plus books include the bestselling Excel Bible and Excel VBA Programming For Dummies as well as several titles in the Mr. Spreadsheet’s Bookshelf series. Visit his popular Spreadsheet Page at spreadsheetpage.com.
978-0-470-47535-5 Wiley and the Wiley logo are registered trademarks of John Wiley & Sons, Inc. and/or its affiliates. Excel is a registered trademark of Microsoft Corporation in the United States and/or other countries.