B
bojan.franic
I have to fix one problem, overlapping DataLabels. Each DataLabel can
have one of two possible colors. Each orange DataLabel must be on top,
and light orange below. I'm talking about depth, not a label position
around point.
How can I do that? Here is a source code:
' add lables to the peak info ranges (if they exist)
For pRangeCounter = pRangeFirstPeak To pRangeLastPeak Step 1
pNumberOfPeakValues =
CInt(Worksheets(pDataSheetName).Cells(pDataRowPeakIndicator,
pColumnCounter).Value)
If pNumberOfPeakValues = CInt(CVErr(xlErrNA)) Then
' do nothing
ElseIf pNumberOfPeakValues > 0 Then
' range exist
' select range and add labels
ActiveChart.SeriesCollection(pRangeCounter).Select
ActiveChart.SeriesCollection(pRangeCounter).ApplyDataLabels
Type:= _
xlDataLabelsShowLabel, AutoText:=True, LegendKey:=False
' go through points
For pPointCounter = 1 To REPORTED_DAYS Step 1
' row lies 1 row further then point number
pDataRow = pPointCounter + 1
' convert data value to numeric
pPeak = CDbl(Worksheets(pDataSheetName).Cells(pDataRow,
pColumnCounter).Value)
If pPeak = CDbl(CVErr(xlErrNA)) Then
' do nothing, skip this cell containing #NV
ElseIf pPeak > 0 Then
' we found a peak
' select label and get info from datasheet
ActiveChart.SeriesCollection(pRangeCounter).Points(pPointCounter).DataLabel.Select
'define formula
pFormula = "=" & pDataSheetName & "!R" & pDataRow &
"C" & pColumnCounter + 1
Selection.Text = pFormula
' put label above
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
End With
If CInt(Worksheets(pDataSheetName).Cells(pDataRow,
pSlaColumn).Value) = pSlaKpi Then
' peak lies within the SLA month
pLabelColor = COLOR_ORANGE
Else
' peak lies not within the SLA month
pLabelColor = COLOR_LIGTH_ORANGE
End If
' color the label
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
With Selection.Interior
.ColorIndex = pLabelColor
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End If
Next pPointCounter
' select datalabels of current range
ActiveChart.SeriesCollection(pRangeCounter).DataLabels.Select
' set font datalabels of current range
Selection.AutoScaleFont = True
With Selection.Font
.Bold = True
.Name = gFontName
.Size = FONT_SIZE_DATA_LABELS
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End If
pColumnCounter = pColumnCounter + pStepDataColumnPeak
Next pRangeCounter
have one of two possible colors. Each orange DataLabel must be on top,
and light orange below. I'm talking about depth, not a label position
around point.
How can I do that? Here is a source code:
' add lables to the peak info ranges (if they exist)
For pRangeCounter = pRangeFirstPeak To pRangeLastPeak Step 1
pNumberOfPeakValues =
CInt(Worksheets(pDataSheetName).Cells(pDataRowPeakIndicator,
pColumnCounter).Value)
If pNumberOfPeakValues = CInt(CVErr(xlErrNA)) Then
' do nothing
ElseIf pNumberOfPeakValues > 0 Then
' range exist
' select range and add labels
ActiveChart.SeriesCollection(pRangeCounter).Select
ActiveChart.SeriesCollection(pRangeCounter).ApplyDataLabels
Type:= _
xlDataLabelsShowLabel, AutoText:=True, LegendKey:=False
' go through points
For pPointCounter = 1 To REPORTED_DAYS Step 1
' row lies 1 row further then point number
pDataRow = pPointCounter + 1
' convert data value to numeric
pPeak = CDbl(Worksheets(pDataSheetName).Cells(pDataRow,
pColumnCounter).Value)
If pPeak = CDbl(CVErr(xlErrNA)) Then
' do nothing, skip this cell containing #NV
ElseIf pPeak > 0 Then
' we found a peak
' select label and get info from datasheet
ActiveChart.SeriesCollection(pRangeCounter).Points(pPointCounter).DataLabel.Select
'define formula
pFormula = "=" & pDataSheetName & "!R" & pDataRow &
"C" & pColumnCounter + 1
Selection.Text = pFormula
' put label above
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Position = xlLabelPositionAbove
.Orientation = xlHorizontal
End With
If CInt(Worksheets(pDataSheetName).Cells(pDataRow,
pSlaColumn).Value) = pSlaKpi Then
' peak lies within the SLA month
pLabelColor = COLOR_ORANGE
Else
' peak lies not within the SLA month
pLabelColor = COLOR_LIGTH_ORANGE
End If
' color the label
With Selection.Border
.Weight = xlHairline
.LineStyle = xlNone
End With
Selection.Shadow = False
With Selection.Interior
.ColorIndex = pLabelColor
.PatternColorIndex = 1
.Pattern = xlSolid
End With
End If
Next pPointCounter
' select datalabels of current range
ActiveChart.SeriesCollection(pRangeCounter).DataLabels.Select
' set font datalabels of current range
Selection.AutoScaleFont = True
With Selection.Font
.Bold = True
.Name = gFontName
.Size = FONT_SIZE_DATA_LABELS
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
End If
pColumnCounter = pColumnCounter + pStepDataColumnPeak
Next pRangeCounter