Adding Axis Labels to a Chart

I want to produce the chart shown in the picture below. I am not able to add the caption for the primary axis. As I step through the code, it deletes the old axis. When I step through the Add new axis: this line

Please Login or Register  to view this content.

does nothing, so this line

Please Login or Register  to view this content.

fails with an "Object Required" error.

If I comment the line out, everything else works fine.

Full code

Please Login or Register  to view this content.

add this line in

Please Login or Register  to view this content.

right before

Please Login or Register  to view this content.

Thanks for the help. I experimented with that, but had ti in the wrong part of the code.

Any idea why you have to do this for the primary axis but not the secondary or category axis and why the macro recorder doesn't suggest it? I'll accept, "It's just one of those Excel things." as an answer.

for some reason
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
this does not automatically make it visible (or just doesnt do anything...cant tell)

or
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleNone)
makes
.Axes(xlValue).HasTitle = false


macro recorder...often leaves out important tidbits while adding numerous other things
i would just add this to another thing you cant extract from recorder

It looks like a bug with that constant. msoElementPrimaryValueAxisTitleNone and msoElementPrimaryValueAxisTitleAdjacentToAxis actually have the same value (306). You could add

Please Login or Register  to view this content.

to the code

Of course, it shouldn't be necessary to activate the chart for this code.

I dislike working with active anything - I'll tell Excel where it should be and what it should be doing. However, when it comes to charts, it seems that you have to activate it somewhere in the process or at least set the chart object.

Originally Posted by dflak

or at least set the chart object.

The same as any other object. Using sh.ChartObjects(ChartName).Chart would be preferable here, in my opinion. There are a few niggles with charts that occasionally require selecting it to work around but none appear to be present in this code.

Xlintwit - I'm going to revise the code based on your comments and then post it as a finished product. Thanks for the tips.

And here it is ...

Invisible ink?