Overlapping DataLabels

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Floating Bar Chart 1004 error 0
add a chart in a Add-In 1
DataLabels 1
Excel hangs 1
Macro Error 1
Apply different color to datalabels 3
Error with calling Format sub 7
Format Data Series in a Pivot Chart 2

Top