Set chart axis title within Macro

[nigelog]

Hi
I have worked out a rudimentary system to change data source for a template chart via macro buttons. When I use though I lose my Category(X)axis labels which should be set as a named range TITLES.

I have tried umpteen inserts into code below but cannot hit on the one I need to always format the x axis labels.

Ant assistance appreciated

Rgds Nigel. Attached code used

Please Login or Register  to view this content.

[Andy Pope]

You need to set the XValues of a series collection.

So code more like this, changing the workbook name to suit.

Please Login or Register  to view this content.

[se1429]

Try this:

ActiveChart.SeriesCollection(1).XValues = Worksheets("Sheet1").Range("Titles")

Change the name of the worksheet as applicable.

It works fine in Excel 2003

[Andy Pope]

Hi se1429,

That will set the axis labels to the range defined by the named range Titles but it will be a static link.

If the range Titles changes the chart will not update.

[se1429]

Hi

I tried changing some cells within the range, and chart got updated on the spot.
May be you have to setup some other chart properties. I am not sure. I hope I could give an immediate answer.

Regards.

[Andy Pope]

Yes, if you change any of the existing cells in the range then yes the chart will update.

I was thinking, maybe too much , that the range was dynamic, in that it could expand or contract.

[nigelog]

Many thanks Andy / Se1429

I had nearly gotten there but forgot the "!" in the reference. Many thanks for your help. Finished article attached for reference. I have one more question to finish it completely but I will post that as a new question as the title of it will have to change.

Many thanks again for all help

Rgds Nigel