help with chart script and data labels

[Chris]

Below is some code I've written which populates a scatter graph with data
extracted from a different work sheet. What I can't work out is changing
the data label names. The script loops through each plot value (case) and
for each iteration I would have liked to change the data label but not its
value....any suggestions?

Public Sub xyGraphs()

'Declare Variables
Dim rngDataSource As Range
Dim iDataRowsCt As Long
Dim iSrsIx As Integer
Dim chtChart As Chart
Dim srsNew As Series
Dim riskRating As String
Dim riskScore As Integer
Dim cChtTitle, xAxisTitle As String
Dim xAxisMax, xAxisMin As Date
Dim yAxisTitle As String
Dim yAxisMax, yAxisMin As Integer
Dim hColour As Long
Dim hSize As Integer
Dim pointLableCol, cPointScoreCol, cPointRatingCol As Integer
Dim pointProximityCol As Date
Dim lastCol As Integer
Dim lastrow As Long
Dim redScore, closedCol As Integer
Dim todaysDate As Long
Dim plotLabelCol As Integer
Dim plotLabelVal As Integer
todaysDate = Date


'Set up chart title and other options
cChtTitle = Worksheets("register").Range("K21") 'Current Situation
Chart Title
xAxisTitle = Worksheets("register").Range("K22") 'X Axis Title
yAxisTitle = Worksheets("register").Range("K23") 'Y Axis Title
xAxisMax = Worksheets("register").Range("K27") 'X Axis Maximum
xAxisMin = Worksheets("register").Range("K26") 'X Axis Minimum
yAxisMax = Worksheets("register").Range("K25") 'Y Axis Maximum date
yAxisMin = Worksheets("register").Range("K24") 'Y Axix Minimum Date
hColour = 255 'High colour Red
hSize = 10 'High Size
pointLableCol = 8 'Series Lable column in sheet 1
cPointScoreCol = 2 'Series current scores Column
pointProximityCol = 4 'Series Proximity column
cPointRatingCol = 8 'Series current Risk Rating Colunm
redScore = 20 'Score above which point is coloured
BLACK
closedCol = 3 'Risk is Closed
plotLabelCol = 1 'Column with risk label value

'Activate Source Sheet & select all rows & columns with data
Sheets("Register").Activate
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastrow = ActiveSheet.Cells(65536, 1).End(xlUp).Row
ActiveSheet.Range("a1:" &
ActiveSheet.Cells(lastrow,lastCol).Address).Select
Set rngDataSource = Selection
iDataRowsCt = lastrow
If iDataRowsCt = 1 Then
Application.Goto ActiveWorkbook.Sheets(selectSheet).Range("D3030")
'select cell
MsgBox ("Sorry - There is no data on the selected sheet!")
Exit Sub
End If

'Create the Current situation chart
Sheets("RiskMatrix").Activate
ActiveSheet.ChartObjects("Chart 19").Activate ''select chart
Set chtChart = Application.ActiveChart

ActiveChart.ChartArea.ClearContents ''clear current
contents

With chtChart
.ChartType = xlXYScatterLines
For iSrsIx = 2 To iDataRowsCt 'loop starting at row 2

''if score is not 0 and proximity not nul and it's not closed
then
If rngDataSource.Cells(iSrsIx, cPointScoreCol) <> 0 _
And rngDataSource.Cells(iSrsIx, closedCol) <> "Live - Draft" _
And rngDataSource.Cells(iSrsIx, pointProximityCol) <> "" Then

'' Add each series
Set srsNew = .SeriesCollection.NewSeries
With srsNew
riskRating =
rngDataSource.Cells(iSrsIx,cPointRatingCol) ''set the case variable
.Name = rngDataSource.Cells(iSrsIx, pointLableCol)
.Values = rngDataSource.Cells(iSrsIx,cPointScoreCol)
.XValues =
rngDataSource.Cells(iSrsIx,pointProximityCol)
Select Case riskRating
Case "Very Severe"
.MarkerBackgroundColor = hColour
.MarkerForegroundColor = hColour
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
Case "Severe"
.MarkerBackgroundColorIndex = 46
.MarkerForegroundColorIndex = 46
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
Case "Material"
.MarkerBackgroundColorIndex = 44
.MarkerForegroundColorIndex = 44
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
Case "Manageable"
.MarkerBackgroundColorIndex = 4
.MarkerForegroundColorIndex = 4
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
End Select
plotLabelVal =
rngDataSource.Cells(iSrsIx,plotLabelCol)
.Smooth = False
.Shadow = False
End With
End If
Next

 

[Smallweed]

Sorry, not in the mood to wade through all that code(!) but I've found the
following to be useful when applying labels. Excel is a bit rigid when you
use the normal menus but with this code, make sure you've typed the labels
you want into a worksheet range somewhere (in the right order) and specify
the range (e.g. A1:A23) in line 5 below. Then, in the chart, select the
particular series you want to apply the labels to and run the code: you need
to be careful you've entered the same number of labels as there are data
points in your series. (You can make this all a bit neater using a userform
with a Ref control to highlight your range of labels):

Sub ApplyLabels()
Dim rng As Range
Dim intCtr As Integer
Selection.ApplyDataLabels
For Each rng In Range("range of cells with data labels in")
intCtr = intCtr + 1
Selection.Points(intCtr).DataLabel.Text = rng.Value
Next
End Sub

 

[Chris]

Sub ApplyLabels()
Dim rng As Range
Dim intCtr As Integer
Selection.ApplyDataLabels
For Each rng In Range("range of cells with data labels in")
intCtr = intCtr + 1
Selection.Points(intCtr).DataLabel.Text = rng.Value
Next
End Sub

The:
Selection.Applydatalabels

does not seem to work...any further clues?

 

[Chris]

the issue i'm running into is the case element of my code which generates
multiple series of the same name. I really am desperate to change the
datalabel text.,...

'' Add each series
Set srsNew = .SeriesCollection.NewSeries
With srsNew
riskRating =
rngDataSource.Cells(iSrsIx,cPointRatingCol) ''set the case variable
.Name = rngDataSource.Cells(iSrsIx, pointLableCol)
.Values = rngDataSource.Cells(iSrsIx,cPointScoreCol)
.XValues =
rngDataSource.Cells(iSrsIx,pointProximityCol)
Select Case riskRating
Case "Very Severe"
.MarkerBackgroundColor = hColour
.MarkerForegroundColor = hColour
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
Case "Severe"
.MarkerBackgroundColorIndex = 46
.MarkerForegroundColorIndex = 46
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
Case "Material"
.MarkerBackgroundColorIndex = 44
.MarkerForegroundColorIndex = 44
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
Case "Manageable"
.MarkerBackgroundColorIndex = 4
.MarkerForegroundColorIndex = 4
.MarkerSize = hSize
.MarkerStyle = xlMarkerStyleTriangle
End Select
plotLabelVal =
rngDataSource.Cells(iSrsIx,plotLabelCol)
.Smooth = False
.Shadow = False
End With
End If
Next

 

[Jon Peltier]

Someplace you have to apply data labels. You've assigned a cell value to an
undeclared variable, but you haven't applied the value to anything.

- Jon

 

[Chris]

hi jon,

the full script is in earlier post...not sure where the issue is...

chris

 

[Jon Peltier]

Do the series have data labels? What do the data labels say? Do all points
in a series get the same label? You have a single line of code that may
involve labels:

plotLabelVal = rngDataSource.Cells(iSrsIx,plotLabelCol)

but you have to actually apply the text of the label to the label. As I look
more closely at the code (do you really indent it to that extent? I find it
hard to follow) I think it is telling me that each series consists of a
single point. So...

With srsNew ' use same With block as already exists
.HasDataLabels = True
.DataLabels(1).Text = plotLabelVal
End With

If I've guessed wrong about the number of points, simply loop through the
points and apply labels to each point:

With srsNew ' use same With block as already exists
.HasDataLabels = True
For iPt = 1 to .Points.Count
.DataLabels(iPt).Text = plotLabelVal
Next
End With

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

 

[Chris]

With srsNew ' use same With block as already exists
.HasDataLabels = True
.DataLabels(1).Text = plotLabelVal
End With

Jon,

Many thanks. The above code placed with the case select loop worked
perfectly!

Chris