On 6 Aug., Excel 009 wrote:
> Hi All,
>
> I have the following data and want to create a scatter chart. I am
> using either 2003 or 2007.
>
> Person X1 X2
> ---------- --- ---
> a 2 4
> b 8 2
> c 9 3
> d 1 5
> e 8.5 1
>
> (The range is =Sheet1!$A$1:$C$6)
>
> I want to put the values (a, b, c, d, e) from the Person column next
> to the data point. For example, d for the (1,5).
>
> Can anyone help?
>
> Excel 009
You can do the following workaround: Make the chart an ordinary
XYScatter chart and add one series for each data point:
Sub CreateChart()
If Selection.Columns.Count <> 3 Then
MsgBox "You have to select exactly 3 columns to create the chart."
Exit Sub
End If
Dim NewChart As Excel.Chart
Set NewChart = ActiveSheet.ChartObjects.Add(100, 100, 100,
100).Chart
NewChart.ChartType = xlXYScatter
' Add the series to the chart: Each row of the selection will end up
as one series.
Dim CurrentRow As Long
For CurrentRow = 0 To Selection.Rows.Count - 1
Dim NewSeries As Excel.Series
Set NewSeries = NewChart.SeriesCollection.NewSeries
NewSeries.ChartType = xlXYScatter
Dim TempString As String
TempString = "=SERIES(" & ActiveSheet.Name & "!R" & (Selection.Row
+ CurrentRow) & "C" & (Selection.Column + 0) & ","
TempString = TempString & ActiveSheet.Name & "!R" & (Selection.Row
+ CurrentRow) & "C" & (Selection.Column + 1) & ","
TempString = TempString & ActiveSheet.Name & "!R" & (Selection.Row
+ CurrentRow) & "C" & (Selection.Column + 2) & ", 1)"
NewSeries.FormulaR1C1 = TempString
''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Customize your series here.
'
' Set the attributes of the series: marker attributes, labels, and
so on.
NewSeries.MarkerStyle = xlMarkerStyleCircle
NewSeries.MarkerBackgroundColor = 1
NewSeries.MarkerForegroundColor = 1
NewSeries.MarkerSize = 5
NewSeries.ApplyDataLabels AutoText:=True, _
LegendKey:=False, _
ShowSeriesName:=True, _
ShowCategoryName:=False, _
ShowValue:=False, _
ShowPercentage:=False, _
ShowBubbleSize:=False
Next CurrentRow
End Sub
The problem is that you'll have to set any attributes of this pseudo-
series through VBA code (the macro recorder will help you most of the
time).
Regards,
Stuart
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
