Automatic Axis Titles in Excel 2007

[ntrsiv]

Hello there, I need to make charts in excel 2007, nothing that fancy.

If I have 2 columns of data...with the first row being the Axis Titles, How
do I make it so that the rows automatically become the axis titles. But
even before that, is there a way to make a Scatter plot, with axis titles
shown to be my default chart type? I know this kind of highlighting and
"charting" used to exist...why all the run around now?

I know, this seems rediculously simple. but it seems to be so simple that
2007 has completely overlooked it. Where did the wizard go?../sigh

 

[Jon Peltier]

Excel doesn't automatically use any cell contents as axis labels, and never
has. But I just modified a little procedure of mine to add an XY chart using
the top cell in the x and y ranges as the axis labels.

Sub ChartWithAxisTitles()
Dim objChart As ChartObject
Dim myChtRange As Range
Dim myDataRange As Range
With ActiveSheet
' What range contains data for chart
Set myDataRange = Application.InputBox( _
prompt:="Select a range containing the chart data.", _
Title:="Select Chart Data", Type:=8)
' What range should chart cover
Set myChtRange = Application.InputBox( _
prompt:="Select a range where the chart should appear.", _
Title:="Select Chart Position", Type:=8)
' Cover chart range with chart
Set objChart = .ChartObjects.Add( _
Left:=myChtRange.Left, Top:=myChtRange.Top, _
Width:=myChtRange.Width, Height:=myChtRange.Height)
' Put all the right stuff in the chart
With objChart.Chart
.ChartArea.AutoScaleFont = False
.ChartType = xlXYScatterLines
.SetSourceData
Source:=myDataRange.Offset(1).Resize(myDataRange.Rows.Count - 1)
.HasTitle = True
.ChartTitle.Characters.Text = "My Title"
.ChartTitle.Font.Bold = True
.ChartTitle.Font.Size = 12
With .Axes(xlCategory, xlPrimary)
.HasTitle = True
With .AxisTitle
.Characters.Text = myDataRange.Cells(1, 1)
.Font.Size = 10
.Font.Bold = True
End With
End With
With .Axes(xlValue, xlPrimary)
.HasTitle = True
With .AxisTitle
.Characters.Text = myDataRange.Cells(1, 2)
.Font.Size = 10
.Font.Bold = True
End With
End With
End With
End With
End Sub


- Jon

 

[ntrsiv]

Jon,

That's excellent, what does it do? (I have an idea that it is a macro) But I
am not sure...is that what it is?..If not, how do I incorporate/use it?

 

[ntrsiv]

Jon,

I see its a VBA procedure,

I copied and pasted into the code window, but there was a problem..should it
be:

..SetSourceData Source:=

or

..SetSourceDataSource:=

What I copied below seems to have a carriage retrun in there after the
..SetSourceData....which resulted in the next line being all red in the VBA
editor.

Past that I don't really know how to save the procedure, and then use it.

 

[Jon Peltier]

Sorry, I forgot about line wrapping. That should be all in one line:

.SetSourceData Source:=blah blah

I've also posted a slightly enhanced version of this procedure in my new
blog:

http://peltiertech.com/WordPress/2008/03/06/quick-vba-routine-xy-chart-with-axis-titles/

I should post a page explaining how to use a procedure like this. Sounds
like you figured out what to do with it, but anyway. From Excel, press
Alt+F11 to open the VB Editor. Find & select your project in the Project
Explorer window (Press Ctrl+R to open this window if it's not visible). Go
to Insert menu > Module. Paste the code in place. If you have any red lines,
there's a syntax error.

To run a procedure, select what needs to be selected in Excel, then either
go to Tools > Macro > Macros (shortcut Alt+F8) and select & run the macro,
or go to the VB Editor, click anywhere within the procedure you want, and
press F5.

You could put a button on the worksheet (from the Forms menu) and assign a
macro to it (right click on the button). You can also assign a macro to a
chart or shape, which runs the macro when the object is clicked on. Or you
could add a button to a toolbar or menu (View > Toolbars > Customize,
Commands tab, Category Macros, add a button, then right click on it until
you find what you need, including Assign Macro.

- Jon