ÂŽ
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 eventhandler 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 event-procedure 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
Wiley, the Wiley logo, For Dummies and all related trademarks, logos, and trade dress are trademarks or 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.
速
Using the NewChart Event in Excel 2010 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 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 About the Author John Walkenbach is principal of J-Walk and Associates, Inc., a leading authority on spreadsheet software and creator of the awardwinning Power Utility Pak. Walkenbach has written more than 50 books and 300 articles for publications including PC World, InfoWorld, and Windows. Visit his popular Spreadsheet Page at spreadsheetpage.com.
978-0-470-50369-0
Wiley, the Wiley logo, For Dummies and all related trademarks, logos, and trade dress are trademarks or 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.