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