Macros to change chart titles?

[daveybohh]

Hi,

I am looking to construct a macros, or getsome code that will allow me to change chart titles throughout a workbook.

For instance, one title may be "Class size January 2011", another "Cost January 2011". These charts are on different sheets within my workbook. I would like a code that I can change "January 2011" to "February 2011" and that will subsequently change all chart titles in my workbook.

Any help is would be greatly appreciated.

Thanks.

 

[GlennUK]

Can I ask why you aren't doing this with formulas? ( i.e. formulas to generate the required titles, and then referring to the calculation with a direct link in each title box )

 

[daveybohh]

Hi Glenn,

Thanks for the quick reply.

I have tried to use formauls and linkng the title to a cell but my titles are quite complex and contain tect with different formatting etc. I have not been able to tranpose the formatting of the text in the reference cell to that of the title. Therefore I thought it would be easier to construct a macros to do the job. Is there a way to include the source formatting in the chart title?

Thanks again.

 

[GlennUK]

It sounds like you are going to need a macro then. But, you will have to handle the formatting in the macro won't you ... so what rules does the formatting have to follow? And what version of Excel are you using?

 

[daveybohh]

I require different font size and also bold to be used in the title. For example:

London
Class Size - February 2011

Would it be possible to apply the formatting to a cell and have the macros simply copy the cell + formatting and paste it into the required chart titles?

I am using Excel 2010.

Thanks.

 

[GlennUK]

So you'd want another macro to first populate those cells with the correct contents, and then you'd go into each cell, apply the required formatting by hand, and then run another macro to copy the cell contents and formatting into the chart titles?

P.S. I don't have access to Excel 2010, and so can't really write any macros for you ( I know that there are a lot of holes in the object model where chart objects are concerned within Excel 2007, which is what I've got )

 

[daveybohh]

Sorry, I am not making myself very clear here. I will try to explain (which I should have done in the first instance!)

I have numerous charts within my workbook that all have different titles, ie class size for example, but all relate to one month. So one title may be

London
Class Size - February 2011

while another would be

London
Cost - February 2011

I have to update these workbooks monthly and would ideally like to change "February 2011" to "March 2011" in all the chart titles. I have tried to use formulae to achieve this but I find that the formatting does not carry over into the chart title which I need. I need to format different words within the title with varying text size/bold etc. If there is a way I can do this it would be the best solution but I have not found one yet.

Therefor I thought macro would be the best bet to take my text from the cell, find "February 2011" and replace it with "March 2011". I am a bit of a rookie with VB so was struggling to do this!

Cheers

 

[GlennUK]

I have tried to use formulae to achieve this but I find that the formatting does not carry over into the chart title which I need.

Can you format different parts of a formula result differently then? I can't do that in Excel 2007. Neither can I manipulate subparts of the ChartTitle in VBA in Excel 2007.

In short, I can't give you a solution to this, if you insist on having the differently formatted texts in the same chart object.

( what you've shown so far is a lump of text of LONDON, which could be stored in a separate textbox, followed by another lump of text in another fontsize ..... but you didn't answer my query about the formatting earlier so I don't know exactly what you're aiming for )