CODEWITHSUNDEEP
vbaexcelchartsexcel-2007
datacentric
datacentric

Reputation: 135

Data Labels in Chart with three decimal points

I am struggling with a part of VBA code that I have written to put customized data labels in excel charts.

Sub LabelPnt() 'change
Dim nm As Variant
Dim rate As Variant
Dim i As Integer
Dim r As Integer
Dim n As Integer
Dim b As Boolean
With Sheets("Rates") 'change as needed
r = .Range("C13:C30").Rows.Count   'change as needed
ReDim nm(r)
nm = .Range("C13:C30") 'num/den change
ReDim rate(r)
rate = .Range("B13:B30")  'rate change
End With
With Sheets("Fairview Northland Rate") 'change
For n = 1 To 1
With .SeriesCollection(n)
        .ApplyDataLabels
    For i = 1 To r
       With .Points(i)
        Select Case n
            Case 1
                .DataLabel.Text = rate(i, n) & Chr10 & "(" & nm(i, n) & ")"
                .DataLabel.Font.Bold = False
                .DataLabel.Font.Size = 7.5
                .DataLabel.HorizontalAlignment = xlCenter
        End Select
       End With
    Next i
End With
Next n
End With
End Sub

In this statement ".DataLabel.Text = rate(i, n) & Chr10 & "(" & nm(i, n) & ")"", the rate has to be three decimal points as in the original data sheet but its excluding the decimal points in data labels in sheets. I am assuming that this is because the data label is text.

Can somebody help me with fixing this? Thanks a ton. I have spent a lot of hours but oculd not figure this part out.

I can provide any further information or clarification, if needed.

Upvotes: 0

Views: 5182

Answers (1)

Jaycal
Jaycal

Reputation: 2087

It's hard to tell what part of the label you want to have with 3 decimal points, but this is the generic code you can use to format any text.

Format(yourValue, "#.000")

For example, if the value in cell A1 is 123.45678...

Format(Range("A1"), "#.000")

would give you the text "123.457"

EDIT

Given your example, update with the following code

.DataLabel.Text = Format(rate(i, n), "#.000") & Chr10 & "(" & nm(i, n) & ")"

Upvotes: 1

Related Questions