• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

Creating custom data labels in a chartseries

Please take a look at the attached workbook. I need to get my chart values from Row 4, but I want to get my data labels from Row 5.

How do I do that? Assuming thatI can have a custom label other than series, category or value.

Thanks,
John
Custom-Data-Labels.xlsx
0
gabrielPennyback
Asked:
gabrielPennyback
2 Solutions
 
Rgonzo1971Commented:
Hi,

Maybe you should use this

http://appspro.com/Utilities/ChartLabeler.htm

regards
0
 
krishnakrkcCommented:
Hi

Put this code in the sheet module. (Right click on tab name > view code > paste the code there in the white pane)

I assume that the sheet has some formulas. The code will fire each time when a calculation happens in the sheet.

Option Explicit

Private Sub Worksheet_Calculate()
    
    Dim rngLabel    As Range
    Dim rngValue    As Range
    Dim i           As Long
    
    Set rngValue = Range("B9:P9")       '<< adjust to suit
    Set rngLabel = Range("B10:P10")     '<< adjust to suit
    
    Application.ScreenUpdating = 0
    For i = 1 To rngValue.Columns.Count
        With rngValue.Cells(1, i)
            .NumberFormat = """Wk """ & .Value & vbLf & """ " & Right(rngLabel.Cells(1, i).Value, 4) & """"
        End With
    Next
    Application.ScreenUpdating = 1
    
End Sub

Open in new window


Kris
0
 
gabrielPennybackAuthor Commented:
Thank you for your solutions. They didn't quite address my actual situation but the two of you got me started in the right direction and I was able to come with a good solution. For what it's worth, this is what I ended up with.

Sub GetDataLabels()
Dim i As Long, Ln As Range
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SetElement (msoElementPrimaryValueAxisShow)
    ActiveChart.ChartArea.Select
    ActiveChart.SeriesCollection(1).Values = "='Weeks Done'!$X$4:$AJ$4"
For i = 1 To 13
Set Ln = Sheets("Weeks Done").Cells(6, i + 23)
    ActiveChart.SeriesCollection(1).Points(i).HasDataLabel = True
    ActiveChart.SeriesCollection(1).Points(i).DataLabel.Text = _
        Left(Sheets("Weeks Done").Cells(5, i + 23), Ln) & vbLf & _
        Right(Sheets("Weeks Done").Cells(5, i + 23), Ln)
    ActiveChart.SeriesCollection(1).Points(i).DataLabel.Position = xlLabelPositionInsideEnd
Next i
    With ActiveChart.Axes(xlValue)
        .MinimumScale = [Lo]
        .MaximumScale = [Hi]
        .MinorUnitIsAuto = True     '.MajorUnit = [XAxisMajor]
    End With
ActiveChart.Axes(xlValue, xlPrimary).Delete
[A1].Select
End Sub

Thanks,
John
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now