Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Retrieve Excel Chart title formula with vba

Posted on 2011-02-14
3
Medium Priority
?
609 Views
Last Modified: 2012-06-27
Hello,

With Excel 2000,
I need to retrieve chart title formula with vba, or a way to remove the title and later on, set it back as it was before I removed it.

Here is why:
I have a macro that copies all charts in the workbook into a powerpoint presentation.
It all works very well, using
Chart.CopyPicture xlScreen, xlPicture 

Open in new window

and then
mySlide.Shapes.Paste

Open in new window

but for visual reasons, the users want the graph title displayed outside the graph in the powerpoint presentation.

So my simple idea was to read the title, remove it, do the copy/paste, replace the title.

I found that I can get the title by using
Chart.ChartTitle.Caption

Open in new window

.
However, if the title is based on a formula (like "=Page!$E$3") the above property returns the value, not the formula. So I can't set it back as it was before I remove it.

I also tried to remove the title using
Chart.HasTitle=False

Open in new window

, but then the whole ChartTitle object is cleared, along with all formating. So setting it back to true does not set it back as it was.

And unfortunately, the Application.Undo won't work in a macro.

Thanks for any help.
0
Comment
Question by:dapcom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 800 total points
ID: 34889557
The usual method (pre 2007 anyway) is to select the title and then use:
strFormula = ExecuteExcel4Macro("GET.FORMULA(SELECTION())")

Open in new window

0
 
LVL 42

Accepted Solution

by:
dlmille earned 1200 total points
ID: 34889835
Here's a tip.  I found this searching and this person had very siimilar challenge to what you're faced with.  Apparently, the way it was resolved was by making the font and color of the chart nonviewable.  Later, you can make it more viewable.  In this approach the formula for the title was never altered.

http://www.excelforum.com/excel-charting/758120-return-chart-title-formula-using-vba.html

Please read down the post as the Asker was resolving almost the identical issue.

Dave
0
 
LVL 1

Author Closing Comment

by:dapcom
ID: 34890641
Thanks to both of you.

I'll try both the Execute4Excel  and non viewable font font ways tomorrow.

Very helpfull.

It would seem I did not search well enough before posting ;)
0

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question