VBA properties of Charts

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Forgive me for I am stumbling through an assignment using charts, so I may misstate or babble here :(

I have a chart that I want a dynamic title on. Thus I believe that I want to plug activechart.charttitle.caption based on input assumptions.

What I'm wondering is how to identify the chart itself. From the 'Chart Window' (from right clicking an empty area of the chart), I see one possible name in the title bar; from activechart.parent.name in Watch window I see "Chart 1" . So for starters I don't know the name of my only chart!

At ANY rate Charts.Count is zero! Charts(1) and charts("Chart 1") are rejected by the Watch window.

Going back to how I started, I guess I'll be okay if I can just know how to make my [sole] chart the "ActiveChart" but beyond that I'm quite clueless. (I don't want to have to manually click the chart to do so either.) Can someone walk me through what I need to do, if this is clear enough?

TIA
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
The Charts.Count will return 0 if you don't have any chart "sheets", not embeded charts in worksheets.

Is that what you have ? embeded charts ?
 
Upvote 0
I suppose yes. I used alt-Insert Chart and its wizard to create it atop source data, then cut it and pasted to an empty sheet.
 
Upvote 0
If a Chart is embedded in a worksheet it is contained in a ChartObject object. Good isn't it?

So if you only have one chart on your worksheet you can use:

ActiveSheet.ChartObjects(1).Chart.ChartTitle.Text = "Whatever"
 
Upvote 0
Outstanding. My meager ability at least permitted me to go
sheets("MySheet").activate


and then your direction worked perfectly.

Way to go!
 
Upvote 0
Anti-Bill -

Gates Is Antichrist said:
I have a chart that I want a dynamic title on. Thus I believe that I want to plug activechart.charttitle.caption based on input assumptions.

I notice that this group is quick to jump on a VBA approach to things, where a more simple worksheet solution would suffice. Or maybe you're all just better programmers than I am.

Anyway, couldn't you put the input assumptions into the worksheet somehow, and write a worksheet formula to construct the chart title text within a cell? If you can (and I'm sure you can), then you can link the title to this cell's contents. Make the chart with any title, then select the title, type an equals sign in the formula bar, and click on this cell with the desired title text and press Enter. When the cell's contents change, the title keeps changes too.

This technique works with chart titles, axis titles, data labels, and after a fashion, with text boxes as well.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______
 
Upvote 0
Jon Peltier said:
I notice that this group is quick to jump on a VBA approach to things, where a more simple worksheet solution would suffice.
LOL! That's a lost cause. Leonardo da Vinci would not be very popular around here.
 
Upvote 0
Re: Dynamic Title for Chart

> Make the chart with any title, then select the title, type an equals sign in the formula bar, and click on this cell with the desired title text and press Enter. When the cell's contents change, the title keeps changes too.


I'm absolutely speechless. I guess the absence of the "refedit" style text box in the Chart properties dialog box would have persuaded most people that such a thing is not possible. (It certainly confounded me!).

Is this a documented feature, or what?
 
Upvote 0
PaddyD said:
see the help file for "Link a chart title or text box to a worksheet cell"

I agree.

It's also worth noting that hidden in this thread there are a couple of hyperlinks to two very good sources for numerous Chart tricks and techniques.

(HINT: both of the authors seem to be fans of "Record Macro")
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top