VBA - Get formula in chart title

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

spectrallypure - 17 Jan 2008 04:58 GMT

Hi all (again)!

It seems that from one day to the next I am suddenly in the need of
performing odd things using VBA.This time I need to get the current
formula on a chart's title. I'll proceed to explain a little bit why I
am needing this so badly.

I have a sheet with tons of charts whose titles contain formulas that
point to the actual cells containing the title strings, which in turn
are formed by means of intricate text concatenations. I need to reuse
all these charts in many other sheets. Unluckily enough, when one uses
formulas in chart titles it's impossible not to include the sheet
references in them (i.e. titles' formulas inevitably have references
in the format "sheet_name!cell"). Thus, when I copy all these charts
to any another sheet, they all bear the references to the old sheet.
Therefore, all the copied charts need to have their titles updated. :(

To summarize, I need to figure out some VBA code to process all these
charts and update the formulas on their titles to point to the correct
sheet names. The problem is that I have not been able to find out how
to get (using VBA) the current formula on a chart's title. Using the
macro recorder doesn't help at all (it seems to have a "depth" limit
on the operations it records -for instance, it can't even record the
procedure of adding a formula in the title on a chart!).

Well, I guess that's it. Please let me know if you have any ideas on
how to accomplish this. ...even any comments on the feasibilty of my
intended solution are pretty much welcome!

Thanks in advance for any help! :)

Jorge Luis.

Reply to this Message

John Bundy - 17 Jan 2008 17:39 GMT

Not sure what you mean by formulas in titles, are you talking about the title
of the graph? Are these in the same place on every sheet? do you also have to
change the references of all of the data sources?

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> Hi all (again)!
>
[quoted text clipped - 28 lines]
>
> Jorge Luis.

Reply to this Message

spectrallypure - 17 Jan 2008 18:23 GMT

Thanks so much for your reply, John. With regard to your comments:

> Not sure what you mean by formulas in titles, are you talking about the title
> of the graph?

Yes, I am indeed talking about the titles of the graphs (charts). I
generated this titles using excel's trick to link them to the contents
of particular cells (i.e. click on the chart title, then on the
address bar, and enter the equal character followed by the address of
the cell with the actual desired contents for the title). Because they
were created this way, all charts bear their in titles *absolute*
references to their corresponding linked cells (by absolute I mean
that the references include the sheet name). This is the root of my
problem: when I copy these charts to other sheets, the sheet
references are NOT updated and they all have their titles pointing to
the original cells, when in reality I need them to point to
corresponding cells on the new sheets.

> Are these in the same place on every sheet? do you also have to
> change the references of all of the data sources?

There is no problem with the cells containing the actual titles. They
are in fact located on the same places on every sheet, and I guess if
the chart titles could use relative references instead of absolute,
they would automatically update to the new sheets' cells, and there
would be no problem at all... :(

So this is why I am in the need of a way to get the formula in a
chart. I plan to use VBA to parse all the copied charts, get the (old)
formulas on their titles, take out the old (original) sheet name and
replace it with the new sheet name, and update the title's formula.
But I am stuck in step 1: get the (old) formulas on their titles!!!

Best regards,

Jorge Luis.

Reply to this Message

John Bundy - 17 Jan 2008 19:16 GMT

I tried to do this through functions but the titles don't allow that. The
following code grabs every chart and changes their title to what is in cell
E1 when you run it. If you need help modifying it let me know.

For i = 1 To ActiveSheet.ChartObjects.Count
ActiveSheet.ChartObjects(i).Activate
ActiveChart.ChartTitle.Caption = Cells(1, 5)
Next

Signature

-John
Please rate when your question is answered to help us and others know what
is helpful.

> Thanks so much for your reply, John. With regard to your comments:
>
[quoted text clipped - 30 lines]
>
> Jorge Luis.

Reply to this Message

spectrallypure - 17 Jan 2008 22:17 GMT

Thanks again for your help, John.

> For i = 1 To ActiveSheet.ChartObjects.Count
> ActiveSheet.ChartObjects(i).Activate
> ActiveChart.ChartTitle.Caption = Cells(1, 5)
> Next

Unfortunately this code doesn't comply with the requisite that each
chart's title points to a different cell. That is why I necessarily
need to get the actual formula of the original titles: I should retain
the reference to the correct cell with the title string, and just
update the sheet name part. When automatically processing a given
chart, I won't know in advance which cell has the corresponding title:
I need to get this information from the current chart's title.

I think an example can be handy. This would be an identical
hypothetical situation:

1. I have sheet "MySheet_1" which contains 100 charts. Each of these
charts has its title pointing to a given cell, as follows:
Chart1 title points to MySheet_1!$A$5, which in turn (let's suppose)
evaluates to the string "This is title of Chart1 on sheet MySheet_1"
Chart2 title points to MySheet_1!$G$2, which in turn (let's suppose)
evaluates to the string "This is title of Chart2 on sheet MySheet_1"
Chart3 title points to MySheet_1!$OP$32, which in turn (let's suppose)
evaluates to the string "This is title of Chart3 on sheet MySheet_1"
...
Chart99 title points to MySheet_1!$FA$25, which in turn (let's
suppose) evaluates to the string "This is title of Chart99 on sheet
MySheet_1"
Chart100 title points to MySheet_1!$B3$72, which in turn (let's
suppose) evaluates to the string "This is title of Chart100 on sheet
MySheet_1"

2. I make a copy of MySheet_1 and call it "MySheet_2". In the copy
sheet, the formulas in the cells with the titles correctly update to
the new desired names:
MySheet_2!$A$5 evaluates to the string "This is title of Chart1 on
sheet MySheet_2"
MySheet_2!$G$2 evaluates to the string "This is title of Chart2 on
sheet MySheet_2"
MySheet_2!$OP$32 evaluates to the string "This is title of Chart3 on
sheet MySheet_2"
...
MySheet_2!$FA$25 evaluates to the string "This is title of Chart99 on
sheet MySheet_2"
MySheet_2!$B3$72 evaluates to the string "This is title of Chart100 on
sheet MySheet_2"

3. All charts in MySheet_2 have wrong titles, since the formulas on
them still point to the cells on the original sheet "MySheet_1"!!!

4. Intended solution: parse every chart in MySheet_2 to:
-Get the current (erroneous) formulas in the titles (MySheet_1!$A$5,
MySheet_1!$G$2, ..., MySheet_1!$B3$72)
-Process the formulas to change the sheet references to reflect the
new sheet's name (MySheet_2!$A$5, MySheet_2!$G$2, ..., MySheet_2!
$B3$72)
-Update the chart titles with the corresponding formulas having the
corrected references.

Of course, this solution only works if one can get the current formula
in the chart titles... any ideas on how to obtain them?

Thanks again for your kind help!

Jorge Luis.

Reply to this Message