Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Creating custom data labels in a chartseries

Posted on 2013-01-23
Medium Priority
369 Views
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
Question by:gabrielPennyback
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 52

Accepted Solution

Rgonzo1971 earned 750 total points
ID: 38813388
Hi,

Maybe you should use this

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

regards
0

LVL 18

Assisted Solution

krishnakrkc earned 750 total points
ID: 38813512
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

Kris
0

LVL 1

Author Closing Comment

ID: 38817055
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
###### Suggested Courses
Course of the Month10 days, 3 hours left to enroll