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

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.R ows.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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"ntrsiv" wrote in message
...
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,

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?

"Jon Peltier" wrote:

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.R ows.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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"ntrsiv" wrote in message
...
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,

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.

"ntrsiv" wrote:

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?

"Jon Peltier" wrote:

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.R ows.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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"ntrsiv" wrote in message
...
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

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/200...h-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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"ntrsiv" wrote in message
...
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.

"ntrsiv" wrote:

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?

"Jon Peltier" wrote:

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.R ows.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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______



"ntrsiv" wrote in message
...
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