Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Chart Title via Macro
Hello all,
Here is what I am trying to do: chtTitle = "='" & chartDataSheetName & "'!$AG3$3" .....ChartTitle.Characters.Text = chtTitle This, of course, does not work because the title ends up being "='Chart Data'!$AG3$3" (without the quotes) rather than the actual formula. In searching the group I found a message posted in 1999 with the following information.... http://groups.google.com/group/micro...a242e5d730580# ****************** BLOCK QUOTE ********************* Is this a flaw in the object model, or am I missing something? It is a flaw (I would have expected .Text to return the visible text, and .Caption to return the formula), but you can always fall back to XLM: 'Activate the chart, then use sTitle = ExecuteExcel4Macro("GET.FORMULA(""Title"")") which returns either the text of the title, or its formula in R1C1 style. Regards Stephen Bullen Microsoft MVP - Excel http://www.BM SLtd.co.uk ****************** BLOCK QUOTE ********************* However, this has not worked for me either. I could be doing it incorrectly. How do I identify the formula that it is getting? i.e. what do I replace "Title" with? I'm building the entire chart from a macro and I have all the labels linking to cells so that last minute changes can be made at the whim of the end user's boss. In other words, I can't just set the title using formula within the macro. It needs to be in the excel sheet. I appreciate any help with this. -Shelton |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Chart Title via Macro
Hi,
Maybe it simply your range reference $AG3$3 ?? Anyway this works in xl2003. Activechart.ChartTitle.Text = "=Sheet1!R3C33" And ExecuteExcel4Macro is for getting the formula used in the chart title rather than setting it. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "shelfish" wrote in message ... Hello all, Here is what I am trying to do: chtTitle = "='" & chartDataSheetName & "'!$AG3$3" ....ChartTitle.Characters.Text = chtTitle This, of course, does not work because the title ends up being "='Chart Data'!$AG3$3" (without the quotes) rather than the actual formula. In searching the group I found a message posted in 1999 with the following information.... http://groups.google.com/group/micro...a242e5d730580# ****************** BLOCK QUOTE ********************* Is this a flaw in the object model, or am I missing something? It is a flaw (I would have expected .Text to return the visible text, and .Caption to return the formula), but you can always fall back to XLM: 'Activate the chart, then use sTitle = ExecuteExcel4Macro("GET.FORMULA(""Title"")") which returns either the text of the title, or its formula in R1C1 style. Regards Stephen Bullen Microsoft MVP - Excel http://www.BM SLtd.co.uk ****************** BLOCK QUOTE ********************* However, this has not worked for me either. I could be doing it incorrectly. How do I identify the formula that it is getting? i.e. what do I replace "Title" with? I'm building the entire chart from a macro and I have all the labels linking to cells so that last minute changes can be made at the whim of the end user's boss. In other words, I can't just set the title using formula within the macro. It needs to be in the excel sheet. I appreciate any help with this. -Shelton |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Chart Title via Macro
N'mind!
I was making that way too dificult. Solution: chtTitle = Range("'" & chartDataSheetName & "'!$AG$3").Value ....ChartTitle.Characters.Text = chtTitle Feeling a bit dumb right now, -S. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Chart Title via Macro
That seems like a convoluted way of getting the text from a cell.
chtTitle = Worksheets(chartDataSheetName).Range("AG3").Value If you want to link the Title to a cell see Andy's post. To get address in R1C1 format see Address in help. Regards, Peter T "shelfish" wrote in message ... N'mind! I was making that way too dificult. Solution: chtTitle = Range("'" & chartDataSheetName & "'!$AG$3").Value ...ChartTitle.Characters.Text = chtTitle Feeling a bit dumb right now, -S. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Chart title and text formula? | Charts and Charting in Excel | |||
Macro for chart title | Charts and Charting in Excel | |||
Using macro to set chart title with a variable month | Excel Discussion (Misc queries) | |||
Formula in Chart Title | Charts and Charting in Excel | |||
Apologies For No Subject. Chart Title via a Macro | Excel Programming |