Macro to add labels to data points in an xy scatter chart

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have searched these boards, and am still without a good solution. Hoping
you can help.

I am aware of add in programs that will achieve this result, but I don't
have that option because of the number of users I have, and permissions to
install on our network. (sucks) I found this item in the microsoft support
pages and am trying to apply the macro in the example.

http://support.microsoft.com/default.aspx?scid=kb;en-us;213750

Everything works fine, the lables attach to the data points as expected, but
I get "Run-time error '1004': Unable to set the HasDataLabel property of
the Point class" when I run the macro. I click end, and the data points show
up.

I don't want to release this spreasheet to users until this issue is worked
out. Any ideas why this is happening, or do you know how to close that run
time error window with VBA?

Thanks so much,

Scott Wagner
 
For some reason, does the particular point that crashes not appear in the
chart, because it's not a plottable value? Such as #N/A or a blank? Since
the point isn't plotted, you can't apply a data label.

You could insert some error evasion code:

For Counter = 1 To Range(xVals).Cells.Count
On Error Resume Next
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
On Error Goto 0
Next Counter

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
That works perfectly!

Thanks!

Jon Peltier said:
For some reason, does the particular point that crashes not appear in the
chart, because it's not a plottable value? Such as #N/A or a blank? Since
the point isn't plotted, you can't apply a data label.

You could insert some error evasion code:

For Counter = 1 To Range(xVals).Cells.Count
On Error Resume Next
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
On Error Goto 0
Next Counter

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
Click to expand...
 
Macro to add data labels to xy scatter chart

I have exactly the same problem and want to know where I should insert your code into my module. This problem only occurs for users of Excel 2003, not 2007.

This is my existing code:

Code: 
Sub AttachLabelsToPoints()
 
	 'Dimension variables.
	Dim Counter As Integer, ChartName As String, xVals As String
	Dim lngChtCounter As Long
	Dim lngSeries As Long
 
	 ' Disable screen updating while the subroutine is run.
	Application.ScreenUpdating = False
 
	For lngSeries = 1 To Chart1.SeriesCollection.Count
		 'Store the formula for the first series in "xVals".
		xVals = Chart1.SeriesCollection(lngSeries).Formula
 
		 'Extract the range for the data from xVals.
		xVals = Mid(xVals, InStr(InStr(xVals, ","), xVals, _
		Mid(Left(xVals, InStr(xVals, "!") - 1), 9)))
		xVals = Left(xVals, InStr(InStr(xVals, "!"), xVals, ",") - 1)
		Do While Left(xVals, 1) = ","
			xVals = Mid(xVals, 2)
		Loop
 
		 'Attach a label to each data point in the chart.
		With Chart1.SeriesCollection(lngSeries)
			lngChtCounter = 0
			For Counter = 1 To Range(xVals).Cells.Count
				If Not Range(xVals).Cells(Counter, 1).EntireRow.Hidden Then
					lngChtCounter = lngChtCounter + 1
					.Points(lngChtCounter).HasDataLabel = True
					.Points(lngChtCounter).DataLabel.Text = Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
					.Points(lngChtCounter).DataLabel.Position = xlLabelPositionCenter
					.Points(lngChtCounter).DataLabel.Font.ColorIndex = 2
					.Points(lngChtCounter).DataLabel.Font.Name = "Arial"
					.Points(lngChtCounter).DataLabel.Font.Size = 11
				End If
			Next Counter
			 If lngSeries = 1 Then .DataLabels.Font.ColorIndex = 2
			 If lngSeries = 2 Then .DataLabels.Font.ColorIndex = 3
		End With
	Next
	Application.ScreenUpdating = True
 
End Sub

Many thanks
Julia
 
Last edited:
Using Macro to add labels to data points

Hi, I need to include labels in my bar chart. I tried to use the code; it does not work. Get errors.

Sub CreateChart()
' Select the cell in the upper-left corner of the chart.
Range("aj10").Select
' Select the current range of data. This line of code assumes that
' the current region of cells is contiguous - without empty rows
' or columns.
Selection.CurrentRegion.Select
' Assign the address of the selected range of cells to a variable.
myrange = Selection.Address
' Assign the name of the active sheet to a variable. This line is
' used in order to allow a chart to be created on a separate chart
' sheet.
mysheetname = ActiveSheet.Name
' Add a chart to the active sheet.
ActiveSheet.ChartObjects.Add(125.25, 60, 301.5, 155.25).Select
' To create a chart on a separate chart sheet, remark out the
' previous line, and substitute the next line for the one above.
' Charts.Add
Application.CutCopyMode = False
' This line can best be written by recording a macro, and
' modifying the code generated by the Microsoft Excel Macro
' recorder.
ActiveChart.ChartWizard _
Source:=Sheets(mysheetname).Range(myrange), _
Gallery:=xlBar, Format:=8, PlotBy:=xlColumns, _
CategoryLabels:=1, SeriesLabels:=1, HasLegend:=0, _
Title:="Student Demographics", CategoryTitle:="Age Group", _
ValueTitle:="", ExtraTitle:=""


End Sub
 
Back
Top