Put the date from a Cell automatically in a Chart Title?

[nbaj2k]

I have a macro and I'm creating a bar graph chart. In the chart title,
I want it to include the date that I have in a specific cell. Is there
a way to call on that in the title?

Basically I have a date range that I will type in and it will create a
chart, but I want the date range to be automatically part of the chart
title.

I know this has to be possible, I've been messing around with the code,
haven't quite gotten it to work yet.

Anyone have any idea?

~J

 

[Peter T]

Record a macro while doing the following

(start with chart not selected and on a different sheet)

Select the chart title
Put the cursor in the input bar and type
"=" followed by reference to the cell

You need to include the entire string in the cell, IOW you cannot combine
cell text with some other text. If necessary put the date in one cell and
link to another that includes the date cell & other text.

This is one of the very few occasions in VBA you actually need to use
..Select

Regards,
Peter T

 

[Jon Peltier]

No need for .Select:

Sub AddChartTitle()
With Worksheets("Sheet1").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "=Sheet1!R3C3"
End With
End Sub

- Jon

 

[Peter T]

Hi Jon,

I should have given a bit more explanation!

The OP asked to link the date in a cell to the title. My system date is
International, today's date in C3 is "24-07-06" for me (24 July).

If I don't .Select the chart title before applying the formula "7-24-06" is
returned to the title. But if the title is selected it returns my
"24-07-06".

I've come across other related problems programmatically putting
international dates in charts notwithstanding any custom number format,
particularly data labels.

Regards,
Peter T

 

[Peter T]

I'm forgetting things, if the cell is edited after applying the formula to
the title the date corrects to my international format. So indeed even in
this scenario it's not necessary to select.

Sub AddChartTitle2()
Dim rng As Range
Set rng = Worksheets("Sheet1").Range("C3") ' "24-07-06"
With Worksheets("Sheet1").ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "=" & rng.Address(, , xlR1C1, True)
End With
'title text = "7-24-06"
rng.Value = rng.Value
' now title text = "24-07-06"
End Sub

Regards,
Peter T