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

Excel VBA - Add Data labels to Bar Chart

Hi Guys,

I have the cde below which takes data from a worksheet called 'ALL' and creates a bar chart. this is working well however I would like the bar chart to display datalabels for the values and can't seem to work out how this is added. we are using Excel 2002.
Can anyone point me in the right direction on how to add data labels to the chart I have created

Thanks


 'Get last populated column for graph
    For c = 1 To 26
                   
        Sheets("ALL").Cells(1, c).Select
        If ActiveCell.Value = Empty Then
            lastColumn = Chr(64 + (ActiveCell.Column - 1))
            Exit For
        End If
                   
    Next
   
    Charts.Add
    ActiveChart.ChartType = xlColumnStacked
    ActiveChart.SetSourceData Source:=Sheets("ALL").Range("A1:" & lastColumn & numProjects + 1), PlotBy:= _
        xlColumns
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:= _
        "Project Defects Graph"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Project Defects"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "QC Project \ Project Name"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Defect Count"
    End With
   
    Sheets("Project Defects Graph").Select
    Sheets("Project Defects Graph").move Before:=Sheets(1)
0
victoriaharry
Asked:
victoriaharry
  • 4
  • 2
1 Solution
 
jo_mCommented:
ActiveChart.ApplyDataLabels

 if I remember correctly it was something along these lines

hope this helps

jo
0
 
dlmilleCommented:
ActiveChart.SeriesCollection(1).ApplyDataLabels 'use this for each series, indexing starting with 1
0
 
victoriaharryAuthor Commented:
Hi, thanks for the fast replys

Can you please explain what is meant by a series. Is it a row of data?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
dlmilleCommented:
If you have a bar chart with one dimension - e.g., you're showing Sales Volume by month - that would be a series.

If you added a bar to that, with say, Sales Earnings by month - now you have two bars per month - that's two series.

Make sense?

Dave
0
 
dlmilleCommented:
Here's an example - one independent variable (their vernacular) is for one series, and there's an example with two series.

http://www.ncsu.edu/labwrite/res/gt/gt-bar-home.html

Dave
0
 
victoriaharryAuthor Commented:
Hi,

I have attached a screenshot of the chart without the data labels. I require a data label for each of the values in the stack. Would this still be considered one series so all I would need would be the code below or are stacked columns treated differently

ActiveChart.SeriesCollection(1).ApplyDataLabels

Thanks

Gavin Chart
0
 
dlmilleCommented:
You can apply one datalabel, but then you'd need to overwrite the label with the real value of the series added together, if I'm reading the bar right.
0

Featured Post

Industry Leaders: 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!

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