• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • 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
John Carney
Asked:
John Carney
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
 
John CarneyReliability Business Tools Analyst IIAuthor 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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