VBA Formatting of Scatterplot chart w/ test labels of data points

Question

Hi all,  I am designing a database for work to create a scatter plot chart based on supplier ratings from 1-10. I recently found coding with VBA that labels each point with the names of the suppliers on the chart at the address:http://excel.tips.net/Pages/T003503\_...ter\_Plots.html

However, problem one now is that I don't know how to manipulate the code and need to change the formatting (font, size, ect)

Also, problem two I have is that I only want some of the points labeled and the others to just be points not labeled zero.

My excel worksheet is set up with:

column A as the company names

B: x-axis points

C: y-axis points

I need two parts to the formatting of the chart:

1. A gradation of green to yellow to red fading from the bottom left corner to the top right
2. Curved lines connecting the 3,5,7,10 on the x-axis to the 3,5,7,10 on the y-axis.
3. Then of course the text labeling of only some data points.

The code that I found is:

Sub DataLabelsFromRange() Dim Cht As Chart Dim i, ptcnt As Integer Set Cht = ActiveSheet.ChartObjects(1).Chart On Error Resume Next Cht.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, _ LegendKey:=False ptcnt = Cht.SeriesCollection(1).Points.Count For i = 1 To ptcnt Cht.SeriesCollection(1).Points(i).DataLabel.Text = _ ActiveSheet.Cells(i + 1, 1).Value Next iEnd Sub

If anyone can point me in the right direction I would really apreciate it! I am lost with programingThanks so so much for your help!!!

Answer 1

(1) What is it that you want to create a gradient for?  The line?  If so, it is very difficult to do in 2003 (or earlier).  For the line segment corresponding to each data point you will have to create an image of a line, color that with a gradient, copy it, and use it in place of the line for the specific data point.  Easier but by no means trivial in 2007 or later.

(2) You have to define what you mean by a 'curved line'  If you mean you want an arc of a circle, plot y=sqrt(n^2-x^2) where n is the intercept you want (3,5,7,etc.) and x varies from 0 to n.

(3) Modify the For loop in your code to For 1 to ptcnt Step X where X=2 for every alternate label, 3 for 1 label every 3 points, etc.  You may also want to look at

TM Chart Leader Lines

http://www.tushar-mehta.com/excel/software/chart_leader_lines/

Hi all,  I am designing a database for work to create a scatter plot chart based on supplier ratings from 1-10. I recently found coding with VBA that labels each point with the names of the suppliers on the chart at the address:http://excel.tips.net/Pages/T003503\_...ter\_Plots.html

However, problem one now is that I don't know how to manipulate the code and need to change the formatting (font, size, ect)

Also, problem two I have is that I only want some of the points labeled and the others to just be points not labeled zero.

My excel worksheet is set up with:

column A as the company names

B: x-axis points

C: y-axis points

I need two parts to the formatting of the chart:

1. A gradation of green to yellow to red fading from the bottom left corner to the top right
2. Curved lines connecting the 3,5,7,10 on the x-axis to the 3,5,7,10 on the y-axis.
3. Then of course the text labeling of only some data points.

The code that I found is:

Sub DataLabelsFromRange() Dim Cht As Chart Dim i, ptcnt As Integer Set Cht = ActiveSheet.ChartObjects(1).Chart On Error Resume Next Cht.SeriesCollection(1).ApplyDataLabels _ Type:=xlDataLabelsShowValue, _ AutoText:=True, _ LegendKey:=False ptcnt = Cht.SeriesCollection(1).Points.Count For i = 1 To ptcnt Cht.SeriesCollection(1).Points(i).DataLabel.Text = _ ActiveSheet.Cells(i + 1, 1).Value Next iEnd Sub

If anyone can point me in the right direction I would really apreciate it! I am lost with programingThanks so so much for your help!!!

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 2

If you only want some data points label then us a range which only has label in the respective cells.

?? I have no idea what these mean with regards to data labels:

1. A gradation of green to yellow to red fading from the bottom left corner to the top right
2. Curved lines connecting the 3,5,7,10 on the x-axis to the 3,5,7,10 on the y-axis.

If you want to add gradient fill to data labels, I'm not sure you can do that in code in 2007.  And regarding #2 - I don't follow at all what you want to do here.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 3

Here is an example of how you would modify your original code to format the data labels as bold

Sub DataLabelsFromRange()

    Dim Cht As Chart

    Dim i As Integer, ptcnt As Integer

    Set Cht = ActiveSheet.ChartObjects(1).Chart

    On Error Resume Next

    Cht.SeriesCollection(1).ApplyDataLabels _

        Type:=xlDataLabelsShowValue, _

        AutoText:=True, _

        LegendKey:=False

    ptcnt = Cht.SeriesCollection(1).Points.Count

    For i = 1 To ptcnt

        Cht.SeriesCollection(1).Points(i).DataLabel.Text = _

        ActiveSheet.Cells(i + 1, 1).Value

        Cht.SeriesCollection(1).DataLabels(i).Font.Bold = True    Next i

End Sub

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 4

Just an FYI - here is a free, little utility to add data labels, I realize it doesn't do everything you are asking for but you might want to consider it.

http://www.appspro.com/Utilities/ChartLabeler.htm

---

I might add the Jon has been kind enough to leave the code exposed.

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire