Dynamic Chart Titles

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".

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

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

Public Sub ChartTitleUpdate(ByVal sWorksheetName As String, _ 
                            ByVal sChartObjectName As String, _
                            ByVal 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

Using Equal Sign

You can also link data labels using the (=)



© 2020 Better Solutions Limited. All Rights Reserved. © 2020 Better Solutions Limited TopPrevNext