Ms Office Tune Up

It’s often handy to include worksheet text in a chart. For example, if the underlying worksheet has a title, you might also want to use that text as your chart title. Similarly, you might have a worksheet value that’s related to the chart, such as an average of the values in a series, and you might want to include that value as part of the chart title or the value axis title. You can simply edit the appropriate chart object’s text by hand to include the value. However, if that value is subject to change (particularly if it’s a

calculated value such as an average), then you need to always update the chart object by hand. Ideally, it is great to use a method whereby the chart text gets updated automatically whenever the worksheet text changes.

In previous versions of Excel, you can do this quite easily by linking a chart text object to a worksheet cell. You do this by selecting the chart text object, typing an equals sign (=) to let Excel know that you want to enter a formula, clicking the worksheet cell containing the text you wanted, and then pressing Enter to confirm the formula. Alas, that easy and straightforward method was dropped in Excel 2007, which now treats chart text objects as pure text boxes that cannot include formulas.

Fortunately, there’s a way to work around this limitation using a bit of VBA code that takes advantage of the fact that chart text objects have a Text property. The idea is that you use VBA to set an object’s Text property equal to the value of a worksheet cell. For example, the chart title corresponds to the ChartTitle object, so you modify the ChartTitle.Text property.

For example, suppose the worksheet title is in cell A1. If you want to use the same text in the chart title, you click the chart to activate it and then enter the following code in the Visual Basic Editor’s Immediate window:

NOTE

With the Visual Basic Editor open (press Alt+F11), you display the Immediate window by choosing View, Immediate Window, or by pressing Ctrl+G.

ActiveChart.ChartTitle.Text = ActiveSheet.Range(”A1″)

This gives you a static chart title. That is, if you change the title in cell A1, the chart title doesn’t automatically update. That’s not a problem for a title that doesn’t change, but what if you use a volatile value such as an average as part of the chart title? For example, given a worksheet that displays the average of a data series in cell C3, here’s a procedure that includes the average in the chart title text:

Public Sub WriteChartTitle()
With ActiveSheet.ChartObjects(1).Chart
.ChartTitle.Text = “Close (Average = ” & Format(ActiveSheet.Range(”C3″), “00.0″) & “)”
End With
End Sub

Figure 1 shows the chart with the text added. (Note that in this procedure I used a more elaborate reference to the chart-ActiveSheet.ChartObjects(1).Chart. This means the chart doesn’t have to be activated to set the title.)

Figure 1 This chart’s title includes the average value calcu­lated in cell C3.

That’s fine, but the title text is still static. How can we get Excel to update the chart title automatically if the average changes? It would be nice if Excel 2007 offered a direct way to do this, but it doesn’t, so we have to take a more roundabout route. Specifically, we have to take advantage of the fact that in Excel, you can trap the SheetChange event, which fires whenever cell data changes in any worksheet. Follow these steps to create a handler for this event:

1. In the Visual Basic Editor, use the Project window to open the VBA project that corre­sponds to the workbook that contains your chart.
2. Double-click the ThisWorkbook item in the project. The Visual Basic Editor opens a module window for the ThisWorkbook object.
3. In the module window, pull down the Object drop-down list (at the top of the module, it’s the list on the left) and select Workbook.
4. Pull down the Procedure drop-down list (at the top of the module, it’s the list on the right) and select SheetChange. The Visual Basic Editor adds the following procedure stub:
   Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   End Sub
5. Type your event handler code between the Sub and End Sub statements.

In the procedure stub, the Sh variable represents the worksheet on which the value changed, and the Target variable represents the Range object (that is, the cell) that was changed. In the example, you should check to see if the name of the sheet is the same as name of the sheet on which the chart resides (Worksheet Text, in this case). If it is, you call the procedure that writes the chart title. Here’s the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name = “Worksheet Text” Then
WriteChartTitle
End If
End Sub

Technorati Tags: worksheet text in chart

Popularity: 3% [?]