In Excel 2007, it is possible to link a chart or an axis title to a cell by using a formula. However this was not possible when using VBA. The correponding objects have now been extended in Excel 2010, as you can see in the sample file which can be downloaded at the end of this article. The following image shows a screenshot of this sample file.
By the way, if you like to refer to a cell manually, you can achieve this by selecting the title object item and the entering address of the cell typing = and the select the cell.
This is shown on the figure below.
The VBA objects „AxisTitle“ und „ChartTitle“ have been extended in Excel 2010 by following properties. However, please note, that Excel 2010 is still Beta. So it’s possible that some changes will be made until the next release.
- Property Formula As String
This property permits to get or set the formula used tor the title object. However it is only possible to use simple assignments like =Sheet!$A$1 and not complex formulas.
- Property FormulaLocal As String
Should set the formula in the user’s language. I don’t really understand the sense of this property due to the limitation above.
- Property FormulaR1C1 As String
Returns or sets the object’s formula in A1-style notation.
- Property FormulaR1C1Local As String
Returns or sets the object’s formula in A1-style notation in the user’s local language.
- Property Height As Double
Returns or sets the height of the object.
- Property Width As Double
Returns or sets the width of the object.
Finally the downloadlink for the sample file:
This article has also been published in German and in English on the following sites:
- Excel Ticker Blog, German
- Professional Excel Solutions by Maninweb.de, English
- Professional Excel Solutions by Maninweb.de, German