Charting Question
 

I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data
below in a spreadsheet and I want column B to be the X-axis and column C to
be the Y-axis how can I easily/automatically add the text in column A as the
data label? Also, oes it matter what order the data is sorted in? I want to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

Charting Question
 

sfrancoe wrote:
I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data
below in a spreadsheet and I want column B to be the X-axis and column C to
be the Y-axis how can I easily/automatically add the text in column A as the
data label? Also, oes it matter what order the data is sorted in? I want to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

-------------------------

Have you tried to turn on the macro recorder, create the chart you want with
everything included and formatted then turn off the recorder and look at the VBA
code it created? I'd start from there.

Bill

Charting Question
 

The problem with that is that the chart wizard doesnt give you the ability to
assign a different value to the labels other than x value or y value. Once
the chart is created there is no way that I can see to assign a different
column to be the data labels.

"Bill Martin" wrote:

sfrancoe wrote:
I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data
below in a spreadsheet and I want column B to be the X-axis and column C to
be the Y-axis how can I easily/automatically add the text in column A as the
data label? Also, oes it matter what order the data is sorted in? I want to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

-------------------------

Have you tried to turn on the macro recorder, create the chart you want with
everything included and formatted then turn off the recorder and look at the VBA
code it created? I'd start from there.

Bill

Charting Question
 

Actually it allows you to do anything you want. Anything you can add manually
to a chart you can record to see how VBA does it. In the SourceDataSeries
section of creating the chart you can fill in a data name. And that does indeed
get recorded into VBA by the macro recorder. Here's what I recorded for a
trivial example with two data lines:

range("A2:C21").Select
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Test").range("A2:C21"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Name = "=""First Data Line"""
ActiveChart.SeriesCollection(2).Name = "=""Second Data Line"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Test"

Alternatively you could put the names of the plot lines in a cell, and just put
the cell address into the Series title box to automatically pull it off the
spreadsheet.

Bill
----------------------------

sfrancoe wrote:
The problem with that is that the chart wizard doesnt give you the ability to
assign a different value to the labels other than x value or y value. Once
the chart is created there is no way that I can see to assign a different
column to be the data labels.

"Bill Martin" wrote:


sfrancoe wrote:

I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data
below in a spreadsheet and I want column B to be the X-axis and column C to
be the Y-axis how can I easily/automatically add the text in column A as the
data label? Also, oes it matter what order the data is sorted in? I want to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

-------------------------

Have you tried to turn on the macro recorder, create the chart you want with
everything included and formatted then turn off the recorder and look at the VBA
code it created? I'd start from there.

Bill

Charting Question
 

thanks but it still is not working. I want each point to have its own label
which is in the column next to the x-axis data. So it would go like this..
coulmn A = Label
column B = X-Axis Value
column C = Y-Axis Value

as i add data to a dynamic range i want the point on the scatter chart to
have the label associated with the data in columns b and c.


"Bill Martin" wrote:

Actually it allows you to do anything you want. Anything you can add manually
to a chart you can record to see how VBA does it. In the SourceDataSeries
section of creating the chart you can fill in a data name. And that does indeed
get recorded into VBA by the macro recorder. Here's what I recorded for a
trivial example with two data lines:

range("A2:C21").Select
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Test").range("A2:C21"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Name = "=""First Data Line"""
ActiveChart.SeriesCollection(2).Name = "=""Second Data Line"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Test"

Alternatively you could put the names of the plot lines in a cell, and just put
the cell address into the Series title box to automatically pull it off the
spreadsheet.

Bill
----------------------------

sfrancoe wrote:
The problem with that is that the chart wizard doesnt give you the ability to
assign a different value to the labels other than x value or y value. Once
the chart is created there is no way that I can see to assign a different
column to be the data labels.

"Bill Martin" wrote:


sfrancoe wrote:

I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data
below in a spreadsheet and I want column B to be the X-axis and column C to
be the Y-axis how can I easily/automatically add the text in column A as the
data label? Also, oes it matter what order the data is sorted in? I want to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

-------------------------

Have you tried to turn on the macro recorder, create the chart you want with
everything included and formatted then turn off the recorder and look at the VBA
code it created? I'd start from there.

Bill

Charting Question
 

I don't understand what you're after, but the question is whether you can
manually set up a chart the way you want it? If so, you can record the process
and thereafter do it from VBA.

If you can't set up what you want manually even, then you can't do it from VBA.

Bill
-----------------------
sfrancoe wrote:
thanks but it still is not working. I want each point to have its own label
which is in the column next to the x-axis data. So it would go like this..
coulmn A = Label
column B = X-Axis Value
column C = Y-Axis Value

as i add data to a dynamic range i want the point on the scatter chart to
have the label associated with the data in columns b and c.


"Bill Martin" wrote:


Actually it allows you to do anything you want. Anything you can add manually
to a chart you can record to see how VBA does it. In the SourceDataSeries
section of creating the chart you can fill in a data name. And that does indeed
get recorded into VBA by the macro recorder. Here's what I recorded for a
trivial example with two data lines:

range("A2:C21").Select
Charts.Add
ActiveChart.ChartType = xlXYScatter
ActiveChart.SetSourceData Source:=Sheets("Test").range("A2:C21"), PlotBy:= _
xlColumns
ActiveChart.SeriesCollection(1).Name = "=""First Data Line"""
ActiveChart.SeriesCollection(2).Name = "=""Second Data Line"""
ActiveChart.Location Whe=xlLocationAsObject, Name:="Test"

Alternatively you could put the names of the plot lines in a cell, and just put
the cell address into the Series title box to automatically pull it off the
spreadsheet.

Bill
----------------------------

sfrancoe wrote:

The problem with that is that the chart wizard doesnt give you the ability to
assign a different value to the labels other than x value or y value. Once
the chart is created there is no way that I can see to assign a different
column to be the data labels.

"Bill Martin" wrote:



sfrancoe wrote:


I have a table of data which I create a scatter graph to show the points. Is
there an easy way to add labels through VBA? For eaxample if I have the data
below in a spreadsheet and I want column B to be the X-axis and column C to
be the Y-axis how can I easily/automatically add the text in column A as the
data label? Also, oes it matter what order the data is sorted in? I want to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

-------------------------

Have you tried to turn on the macro recorder, create the chart you want with
everything included and formatted then turn off the recorder and look at the VBA
code it created? I'd start from there.

Bill

Charting Question
 

HI sfrancoe,

Chart Labeler (by Rob Bovey) http://appspro.com
Chart Tools by (John Walkenbach) http://j-walk.com/ss
Label Chart Points (by me) http://www.edferrero.com/charting.aspx

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

I have a table of data which I create a scatter graph to show the points.
Is
there an easy way to add labels through VBA? For eaxample if I have the
data
below in a spreadsheet and I want column B to be the X-axis and column C
to
be the Y-axis how can I easily/automatically add the text in column A as
the
data label? Also, oes it matter what order the data is sorted in? I want
to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

Charting Question
 

Hi Ed

Thanks for the links and I am sure they work fine but the problem is that I
am trying to get this solution at work and my company won't let me download
any third party addins. I have some code that actually automatically adds
labels (see below) but my problem is that it appears the data has to be
sorted in ascending order by X value. Otherwise the labels don't match up
correctly.

Sub LittleChart_AttachLabelsToPoints()

' Dimension Variables.
Dim Counter As Integer, ChartName As Variant
Dim SourceWorksheet As Variant, xVals As Variant, xCell As Variant
Dim xLabel As Variant
Dim miniChart As ChartObject

On Error GoTo attachError

Set miniChart = ActiveSheet.ChartObjects(1)

' Store the name of the active chart in "ChartName".
ChartName = miniChart.Name

' Store the definition of the first series in "xVals".
xVals = miniChart.Chart.SeriesCollection(1).Formula

' These lines extract the name of the source worksheet from xVals.
SourceWorksheet = Left(xVals, InStr(1, xVals, "!") - 1)
SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - InStr(1,
SourceWorksheet, "("))
If Left(SourceWorksheet, 1) = "," Then
SourceWorksheet = Right(SourceWorksheet, Len(SourceWorksheet) - 1)
End If

' Replace the actual source worksheet name with "xlsheet" so that the
' searches that follow will work correctly if the worksheet name
' contains commas.
xVals = Application.Substitute(xVals, SourceWorksheet, "xlSheet")

' More Processing of the xVals name.
xVals = Right(xVals, Len(xVals) - InStr(1, xVals, ","))

' If the chart is using "assumed" x-values then show an alert
' message.
If Left(xVals, 1) = "," Then
MsgBox "This X-Y scatter chart is using assumed x values. The macro
cannot contine."
' Exit the subroutine if "assumed" x-values are in use.
Exit Sub
End If

' More processing of the xVals name.
xVals = Left(xVals, InStr(1, xVals, ",") - 1)

' Put the original source worksheet name back into xVals, replacing
' "xlSheet".
xVals = Application.Substitute(xVals, "xlSheet", SourceWorksheet)

' Initialize a counter.
Counter = 1

' For each cell in the range xVals...
For Each xCell In Range(xVals)

'Get the value of the label next to the current x-value.
xLabel = xCell.Offset(0, -1).Value
If Len(xLabel) < 1 Then
Exit Sub
End If

'Attach a label to the current data point in the chart.
miniChart.Chart.SeriesCollection(1).Points(Counter ).HasDataLabel = True

'Put the text("DataPoint1",for example) into the attached label.
miniChart.Chart.SeriesCollection(1).Points(Counter ).DataLabel.Text =
xLabel

'Increment the counter.
Counter = Counter + 1
Next xCell 'loop until all done

Done:
'Clean variables
Set miniChart = Nothing

ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 9
End With
Exit Sub

attachError:
MsgBox Err.Number & Err.Description, vbOKOnly
GoTo Done

ActiveChart.SeriesCollection(1).DataLabels.Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 8
End With
End Sub

"Ed Ferrero" wrote:

HI sfrancoe,

Chart Labeler (by Rob Bovey) http://appspro.com
Chart Tools by (John Walkenbach) http://j-walk.com/ss
Label Chart Points (by me) http://www.edferrero.com/charting.aspx

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

I have a table of data which I create a scatter graph to show the points.
Is
there an easy way to add labels through VBA? For eaxample if I have the
data
below in a spreadsheet and I want column B to be the X-axis and column C
to
be the Y-axis how can I easily/automatically add the text in column A as
the
data label? Also, oes it matter what order the data is sorted in? I want
to
sort by column A. Thanks

A B C
1 Florida 2.5 500
2 Maine 2.7 300

Charting Question
 

Hi sfrancoe,

Here is the code I use.

Sub LabelPoints()
Dim pts As Points
Dim LabelRange As Range
Dim stRow As Integer
Dim stCol As Integer
Dim i As Integer

' This example assumes that data and data labels are in columns

' use the input box function to allow the user to select a range
Set LabelRange = Application.InputBox(prompt:="Enter Range containing
Labels" _
& Chr(13), Type:=8)

stRow = LabelRange.Row
stCol = LabelRange.Column

' you could prompt for chart name here
Set pts = ActiveSheet.ChartObjects("Chart 1").Chart. _
SeriesCollection(1).Points

' now show the data label for each point and set the text
' to that in the selected range
For i = 1 To pts.Count
pts(i).ApplyDataLabels Type:=xlShowValue
pts(i).DataLabel.Text = "=Sheet1!R" & i + stRow - 1 & "C" & stCol
Next i

End Sub

Ed Ferrero
Microsoft Excel MVP
http://www.edferrero.com

Hi Ed

Thanks for the links and I am sure they work fine but the problem is that
I
am trying to get this solution at work and my company won't let me
download
any third party addins. I have some code that actually automatically adds
labels (see below) but my problem is that it appears the data has to be
sorted in ascending order by X value. Otherwise the labels don't match up
correctly.