Chart title: Text and Formula together? VBA

[KeriM]

I'm trying to use Excel VBA to autocreate a chart title based on a cel
value. This is what I've got so far:


Code
-------------------

.ChartTitle.Text = "Progress (as of " & "='OtherSheet'!R6C7" & ")"

-------------------


It's supposed to say (as an example): Progress (as of August 28,2012)

The "August 28, 2012" is written in a cell in another sheet. I've trie
double quotes, I've tried putting the = before the text and in th
formula. The above code results everything written out as text
including the formula. It is just listing the formula as text, and it'
not actually getting the value of that cell. Does anyone know th
correct syntax to get this to work

 

[Don Guillett]

I'm trying to use Excel VBA to autocreate a chart title based on a cell

value. This is what I've got so far:





Code:

--------------------



.ChartTitle.Text = "Progress (as of " & "='OtherSheet'!R6C7" & ")"



--------------------





It's supposed to say (as an example): Progress (as of August 28,2012)



The "August 28, 2012" is written in a cell in another sheet. I've tried

double quotes, I've tried putting the = before the text and in the

formula. The above code results everything written out as text,

including the formula. It is just listing the formula as text, and it's

not actually getting the value of that cell. Does anyone know the

correct syntax to get this to work?

Try this
Sub charttitlesas()
With ActiveSheet.ChartObjects("Chart 2").Chart
..HasTitle = True
..charttitle.Characters.Text = "My Date is " & _ sheets("sheet10").Range("e1")
End With
End Sub

 

[KeriM]

;1605033']On Tuesday, August 28, 2012 11:10:03 AM UTC-5, KeriM wrote:-

I'm trying to use Excel VBA to autocreate a chart title based on cell

value. This is what I've got so far:





Code:

--------------------



.ChartTitle.Text = "Progress (as of " & "='OtherSheet'!R6C7" & ")"



--------------------





It's supposed to say (as an example): Progress (as of August 28,2012)



The "August 28, 2012" is written in a cell in another sheet. I'v tried

double quotes, I've tried putting the = before the text and in the

formula. The above code results everything written out as text,

including the formula. It is just listing the formula as text, an it's

not actually getting the value of that cell. Does anyone know the

correct syntax to get this to work?

Try this
Sub charttitlesas()
With ActiveSheet.ChartObjects("Chart 2").Chart
..HasTitle = True
..charttitle.Characters.Text = "My Date is " & _
sheets("sheet10").Range("e1")
End With
End Sub

That works! Thank you