I would like to share this code for those who wish to make an XYScatter Chart using macro. I'm not an expert in vba but I'm sharing this to return my gratitude to those who helped me come up with this code. Attached is an excel file (.xlsm) for testing.
Sub AddXYScatterChart()
'This macro removes existing chart on active worksheet
'and creates an XYScatter Chart with only markers.
'The data labels are placed above the points showing the series name
Dim ChtObj As ChartObject
Dim Cht As Chart
Dim ObjSeries As series
Dim LR As Long
Dim RngData As Range
Dim RngItem As Range
Dim SrsPts As Long
'Delete existing Charts in active worksheet
For Each wsItem In ThisWorkbook.Worksheets
For Each ChtObj In wsItem.ChartObjects
ChtObj.Delete
Next
Next
'Set the last row with data
LR = Range("A65536").End(xlUp).Row
'Set the range of Chart Data
Set RngData = Range("A2:C" & LR)
'Create Chart
Set ChtObj = ActiveSheet.ChartObjects.Add _
(Left:=150, Top:=0, Width:=300, Height:=300)
With ChtObj.Chart
'Set Chart type
.ChartType = xlXYScatter
'Define series from range
For Each RngItem In RngData.Rows
With .SeriesCollection.NewSeries
.Name = RngItem.Cells(1, 1)
.XValues = RngItem.Cells(1, 2)
.Values = RngItem.Cells(1, 3)
End With
'Set Chart properties
.SetElement msoElementLegendNone
Next
'Set marker style and color
For Each ObjSeries In .SeriesCollection
ObjSeries.MarkerStyle = xlMarkerStyleCircle 'Sets the type of marker used
ObjSeries.MarkerSize = 5 'Sets the size of the marker
ObjSeries.MarkerBackgroundColor = RGB(0, 0, 0) 'Adjust to desired color (0 values = black)
ObjSeries.MarkerForegroundColor = RGB(0, 0, 0) 'Adjust to desired color (0 values = black)
'Show series name above points
.SetElement msoElementDataLabelTop
With ObjSeries
SrsPts = .Points.Count
ObjSeries.Points(SrsPts).ApplyDataLabels _
Type:=xlDataLabelsShowValue, _
AutoText:=True, _
LegendKey:=False
ObjSeries.Points(SrsPts).DataLabel.Text = ObjSeries.Name
End With
Next
.SetElement msoElementPrimaryCategoryGridLinesMajor
.SetElement msoElementPrimaryCategoryAxisNone
.SetElement msoElementPrimaryValueAxisNone
'Remove Chart Area border
With .ChartArea.Format.Line
.Visible = msoFalse
End With
'Set X-axis scale value
With .Axes(xlValue)
.MinimumScale = 0
.MaximumScale = 10
.MinorUnit = 0.5
.MajorUnit = 5
End With
'Set Y-axis scale value
With .Axes(xlCategory)
.MinimumScale = 0
.MaximumScale = 10
.MinorUnit = 0.5
.MajorUnit = 5
End With
End With
End Sub
I hope this helps in any of your queries.
By the way, the code is open for suggestion and revision.
__________________________________________________________________________
Credits to:
- Mr. John Peltier https://mvp.support.microsoft.com/profile/Jon.Peltier
- Andy Pope http://www.andypope.info
Cheers!
Bookmarks