Can Excel automatically insert the column header as the axis title?

**galaxytraveler42** · 09-18-2013, 12:12 PM

I make tons of scatter plots and I am getting tired of always having to go in and insert the vertical and horizontal axis titles afterwards and writing them.
Are there a way of Excel automatically including axis titles in all new scatter plots and even better if it could be set per default to use the column header as the axis title?
This would be such a time saver if it exist.
(I am using Excel Mac 2011)

I don't know if a macro can do it.
I have tried to record one, but even though I have read somewhere that I in the axis title can write "= A1" and then it would write the header in column A, then it doesn't work. And I also don't want it to always be A1 but whatever the column is.
If I could make my dream macro, I would want to be able to play it, when I have selected two columns. It should make a scatter plot of them, then make axis titles with the column headers, and have the axis titles, axis number and legend increase their size to 14.
As this is what I do many times a day at the moment.

Can anyone help?

**mrice** · 09-18-2013, 02:11 PM

Try this

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010.

**galaxytraveler42** · 09-19-2013, 05:28 AM

Thank you Martin, it looks like my dream macro is possible after all. Thanks for taking the time to write this for me.

I am however running into a compilation error, when I try to run it. It states "Compile error: Argument not optional" and it is highlighting the first line: "Sub CreatScatterChart()". The way I ran it, was that I selected two columns, went to Tools-macro-macros and then selected the CreatScatterChart, and clicked run, and it came up with the error. Does it run without problems when you try to do the same on your computer? Do you have any ideas of how I can get it to work.

I have unfortunately never worked with programming in VBA.

**mrice** · 09-19-2013, 07:01 AM

Please see the attached.

You can run the macro by either hitting the button or pressing Control l (lower case L).

**galaxytraveler42** · 09-19-2013, 08:36 AM

I get the same error no matter what.
I made a little screen shot video of what I do, hoping that you can see if I do something wrong:
http://youtu.be/YpxLwP8iUT0
or what the problem is.
Thanks again for trying to help me, I really appreciate it

**mrice** · 09-19-2013, 08:49 AM

Just noticed that you are using a Mac - my solution was developed on a Windows machine. Maybe a Mac developer can comment further.

**mrice** · 09-19-2013, 09:29 AM

Can you record a macro creating a chart manually and post the code that is recorded? - it might be possible to see what differences there might be.

**galaxytraveler42** · 09-19-2013, 11:07 AM

Thanks you for still trying
I tried to make a macro doing the same by recording what I did, which means it ends up differently. I had to take out part of it afterwards, as it didn't work when I tried to run it.
When I tried to run yours, it seems like it always hightlights the first line and .Character in the line
ActiveChart.ChartTitle.Format.TextFrame2.TextRange.Characters.Text = MyTitleL

I tried to take the line out, and Excel crashed when I try to run it, after I had chosen the range and the title.

I have tried to line by line to include your into mine, and when I got to the line mentioned above it didn't crash but came with the error
Run-time error '438': Object doesn't support this property or method.

This was what I was comparing with. the macro I did:

Please Login or Register  to view this content.

Moderator Note:

Pls use code tags around your code next time as per forum rules.

**mrice** · 09-20-2013, 03:13 AM

Can you giive this a try ...

Please Login or Register  to view this content.

**Andy Pope** · 09-20-2013, 04:17 AM

haven't got a mac to confirm but will this change the font size of the title?

Please Login or Register  to view this content.

**galaxytraveler42** · 09-20-2013, 06:12 AM

It's working!!!!!! (Dancing around the office... New office mate looking at me weird)
Thank you so much for sticking this one out!

Just brilliant. Can't believe I am so lucky.

I also figured out how to change the front size of the axis numbers finally.

So the final code, that works on mac's look like this:

Please Login or Register  to view this content.

Thanks again

**galaxytraveler42** · 09-20-2013, 06:17 AM

Thanks Andy for looking into my problem, I got the line to work by changing it to:

Please Login or Register  to view this content.

Can Excel automatically insert the column header as the axis title?

LinkBack

Thread Tools

Rate This Thread

Display

Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

Re: Can Excel automatically insert the column header as the axis title?

It is solved

Re: Can Excel automatically insert the column header as the axis title?

Thread Information

Users Browsing this Thread

Similar Threads

Column Header to = Saved File's Title

Automatically copy row header to column header...

How to insert superscripted value in Y-axis title of excel sheet

[SOLVED] Can XLS automatically insert file properties fields into a header

In Excel, sorting columns automatically by clicking column title

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions