Inserting data into Chart Heading

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can anyone help with formatting a chart that is generated at the end of a macro.

My macro is pretty simple - calculates averages etc on some columns of data then plots some of the data in an XY chart.

As this done a large number of times for different data sets, I wish to have the chart heading reflect the data it came from i.e get the average speed from one cell and average load from another cell and then put this in the heading.

The degree of difficulty increases by the fact that the averages I want to use are numbers from calculations and I want to seperate them by some text...

for example, heading would be: "blah blah" "xxxx" "rpm" "yyy" load
where xxxx and yyy are the averages I wish to pick up from the worksheet.

Any help from macro masters would be appreciated.
 
Hi Hume

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

Firstly, you could compile the heading you want into a single string a
part of the macro you use to create the chart. Using your example, an
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 whe
your numbers change.

Secondly, for a dynamic heading, compile your heading string by using
formula in a cell in the worksheet, 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 lin
the chart title to that cell’s value:

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