help with chart script and data labels
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("D30:D30" ) '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 |
help with chart script and data labels
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" wrote: 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("D30:D30" ) '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 |
help with chart script and data 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 The: Selection.Applydatalabels does not seem to work...any further clues? |
help with chart script and data labels
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 |
help with chart script and data labels
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chris" wrote in message ... 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 |
help with chart script and data labels
hi jon,
the full script is in earlier post...not sure where the issue is... chris "Jon Peltier" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chris" wrote in message ... 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 |
help with chart script and data labels
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" wrote in message ... hi jon, the full script is in earlier post...not sure where the issue is... chris "Jon Peltier" wrote in message ... 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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Chris" wrote in message ... 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 |
help with chart script and data labels
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 |
All times are GMT +1. The time now is 04:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com