Scatter XY Plot & Macro Help needed - adding/removing data labels

[gkisystems]

I have an XY Scatter plot chart in Excel in which the X-axis represents sales dollars and the Y-axis represents profit margin. I have several thousand data points, but I need to be able to only look at sub-sets of this data which I have by adding a filter to my data in a different tab in Excel (columns A-F). So far, so good.


Next, I needed to be able to add data labels so the customer name is displayed on each point. I used the macro in this help article here and it works like a charm:
How to use a macro to add labels to data points in an xy scatter chart or in a bubble chart in Excel


Sub AttachLabelsToPoints()


'Dimension variables.
Dim Counter As Integer, ChartName As String, xVals As String


' Disable screen updating while the subroutine is run.
Application.ScreenUpdating = False


'Store the formula for the first series in "xVals".
xVals = ActiveChart.SeriesCollection(1).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.
For Counter = 1 To Range(xVals).Cells.Count
ActiveChart.SeriesCollection(1).Points(Counter).HasDataLabel = _
True
ActiveChart.SeriesCollection(1).Points(Counter).DataLabel.Text = _
Range(xVals).Cells(Counter, 1).Offset(0, -1).Value
Next Counter


End Sub


Now, here's where I need some help:


Q1. How do I create another macro that will delete or hide all the customer names? I'd like to toggle the customer name data labels on and off at will. I've Googled for some solutions, but they do not seem to work.


Q2. I need to have multiple Legend Entires (Series). As soon as I split up my data manually into the 5 different customer types, the first macro (that adds customer names) stops working. Why is this and how do I fixed it?

 

[gkisystems]

Looks like I was able to answer my own question on this one. The solution is to download XY Chart Labeler from appspro.com (Rob Bovey)

 

[Nadeem_Dawood]

I've tried the VBA code in the message above (from the Microsoft support website) and it worked in Excel 2003.
Now that I'm using Excel 2010, the code does not work.

I get an error - Run-time error '91':
Object variable or With block variable not set

Please can someone assist!

 

[gamtoos]

I've tried the VBA code in the message above (from the Microsoft support website) and it worked in Excel 2003.
Now that I'm using Excel 2010, the code does not work.

I get an error - Run-time error '91':
Object variable or With block variable not set

Please can someone assist!

I have the same problem. Try selecting the plotted chart and then run the Macro to get it to work.
It requires a specific object to be activated in VBA but I have not yet figured out how.

 

[gamtoos]

Try adding a the below statement clearing the label into a similar loop:
ActiveChart,SeriesCollection(1).ApplyDataLabels (xlDataLabelsShowNone)