Problem adding Y axis Title

[sdingman]

I recorded a macro to add axis titles. All of the axis titles appeared after recording the macro. The code appears below. However after recording this macro, I deleted my chart sheet, then recreate it, then tried to run my 'Add_Axis_Titles' macro. It stepped through until I got to the line:

Selection.Caption = "Battery %"

Where I got the error: Run-time error '424': Object Required

I could see where the Text box for the secondary Y axis is, and I could see where the X axis title was set to "Time", but the primary Y axis text box was nowhere to be found. I have tried moving the chart, thinking it may be out of sight, but it's not there. I have also recreated the chart and tried manually to add axis titles again, and I was able to add all of the axis titles manually. Does anyone have any Ideas about this?

Please Login or Register  to view this content.

So it appears there is something wrong with:

Please Login or Register  to view this content.

Steve

[6StringJazzer]

I am not sure what the problem is but I would not select everything to set it; you can reference it directly. This may not help but is a cleaner method.

Please Login or Register  to view this content.

[sdingman]

6StringJazzer, Thank you for your response. I pasted what you suggested into the macro, and remmed out what I had. I got exactly the same error. I think I'll try to put a Y axis title only, see what I get.

[sdingman]

When I ran only the code to place a Y axis title, by itself, I got the same error. The Y axis Text Box is just not appearing after the line:

Please Login or Register  to view this content.

Any thoughts as to what might be happening? Is there a test I could perform maybe?
Steve

[6StringJazzer]

I believe this page will answer your question. Your code is from a macro recorder, which is not foolproof.

Try adding this line of code after you set the axis:

Please Login or Register  to view this content.

I do not have a file set up to try this but your symptoms are exactly like the ones on that page.

[sdingman]

6StringJazzer, I placed that line at the top of my macro. When I stepped through that line in my macro, the Y-axis text box appeared. So that does seem to be the issue. Since it worked for the primary Y axis, I applied a similar line for the Secondary Y axis, and it also worked. I thought it would also work for the X axis, but when I tried to step through x-axis line:

Please Login or Register  to view this content.

it failed. I got an error on both lines. I tried them one at a time. I thought the syntax would be similar, but evidently neither one of these works for the x-axis. I wasn't if the x-axis could have a secondary axis or not. The error was:
Run-Time error '-2147467259 (80004005)': Method 'Axes' of object '_Chart' failed

I would have thought that the syntax would be similar.
Steve

[6StringJazzer]

XlCategory is spelled wrong in both places the code you just posted.

Also I think this should go after the SetElement code, rather than at the top, but I could be mistaken.

[sdingman]

6StringJazzer, Thank you so much. Steve