BET: Microsoft Excel Charts Common Problems

Leading the way in Microsoft Office Development

Home

Excel

Word

Microsoft Excel > Charts Common Problems > Dynamic Chart Titles

< Previous | Next >

How to create a dynamic chart title

	It is possible to link a chart title to an individual cell by using a regular formula.
	Select the chart title and use the Formula Bar to enter a cell reference.
	Every time the contents of cell "B2" changes the chart title will be automatically updated.

	It is possible to use some VBA code to automatically update a chart title.
	Before you write any VBA code you must give your chart a unique name.
	You can do this by holding down the Ctrl key while you select the chart object.
	The name of the chart will be displayed in the Name Box to the left of the Formula Bar.
	You can change the name of the chart by typing a different name directly into the Name Box and pressing Enter.

	The subroutine will run automatically when the workbook is opened.
	For this example we have given our chart the name "DynamicTitle".

1
2
3

Private Sub Workbook_Open()
Call ChartTitleUpdate("Sheet1", "DynamicTitle", "C5")
End Sub

The other subroutine should be placed in a normal code module.

4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

Public Sub ChartTitleUpdate(sWorksheetName As String, _
                            sChartObjectName As String, _
                            sCellReference As String)
   Dim objChartObject As ChartObject
   Dim objCalculation As Excel.XlCalculation

   Set objChartObject = Worksheets(sWorksheetName).ChartObjects(sChartObjectName)
   objCalculation = Application.Calculation
   Application.Calculation = xlCalculationAutomatic
   Worksheets(sWorksheetName).Range(sCellReference).Calculate
   objChartObject.Chart.ChartTitle.Text = Worksheets(sWorksheetName).Range(sCellReference).Value

   Application.Calculation = objCalculation
   Set objChartObject = Nothing
End Sub

< Previous | Top | Next >

Leading the way in Microsoft Office Development

How to create a dynamic chart title

It is possible to link a chart title to an individual cell by using a regular formula.

Select the chart title and use the Formula Bar to enter a cell reference.

Every time the contents of cell "B2" changes the chart title will be automatically updated.

It is possible to use some VBA code to automatically update a chart title.

Before you write any VBA code you must give your chart a unique name.

You can do this by holding down the Ctrl key while you select the chart object.

The name of the chart will be displayed in the Name Box to the left of the Formula Bar.

You can change the name of the chart by typing a different name directly into the Name Box and pressing Enter.

The subroutine will run automatically when the workbook is opened.

For this example we have given our chart the name "DynamicTitle".

The other subroutine should be placed in a normal code module.