Re: Inserting data into Chart Heading

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Nicky (Nicky.1acu9n_at_excelforum-nospam.com)
Date: 08/02/04 

Date: Mon, 2 Aug 2004 04:33:25 -0500

Hi Hume

There are two ways to do this, both fairly straightforward, using
concatenation to create the title text you want (concatenate is what
excel calls the process of joining several text strings into one text
string)

Firstly, you could compile the heading you want into a single string as
part of the macro you use to create the chart. Using your example, and
assuming the numbers you want are in cells A1 and B1 on Sheet1:

Sub add_heading()

'note the use of format to limit the number of decimals
headtoadd = "blah blah " & Format(Sheets("Sheet1").Range("a1").Value,
"0.0") & " rpm " & Format(Sheets("Sheet1").Range("b1").Value, "0.0")

With ActiveChart
HasTitle = True
ChartTitle.Characters.Text = headtoadd
End With

End Sub

This is simplest, but is not dynamic - the heading will not change when
your numbers change.

Secondly, for a dynamic heading, compile your heading string by using a
formula in a cell in the work***, again using concatenation, eg

the formula in cell C1 is:
="blahblah "&TEXT(Sheet1!A1,"0.0")&" rpm "&TEXT(Sheet1!B1,"0.0")

then include the following code in your chart-creating macro to link
the chart title to that cell’s value:

With ActiveChart
HasTitle = True
ChartTitle.Text = "=Sheet1!R1C3"
End With 

---
Message posted from http://www.ExcelForum.com/
Quantcast