CODEWITHSUNDEEP
excelvba
StackJP
StackJP

Reputation: 681

VBA - Write values inside bar chart

So I've this chart that is made trought VBA

enter image description here

And now i want to put the data inside bars, so it will look like this:

enter image description here

Can you help?

The code that i've to get to this chart is:

Set rng = ActiveSheet.Range("A" & x & ":C" & (x + 4))
    ' in brackets (Left, Top, Widht, Height) >> modify according to your needs
    Set cht = ActiveSheet.ChartObjects.Add(550, 100 * (i - 4), 300, 100)
    With cht
        .Chart.SetSourceData Source:=rng
        .Chart.PlotBy = xlColumns
        .Chart.ChartType = xlBarStacked
        .Chart.HasAxis(xlValue) = False
        .Chart.HasLegend = False
        .Chart.HasTitle = True
        .Chart.ChartTitle.Text = Application.Sheets(i).Name
        .Chart.ChartTitle.Font.Size = 10
        .Chart.Axes(xlValue).HasMajorGridlines = False
        .Chart.Parent.Name = Application.Sheets(i).Name
    End With
    cht.Activate
    ActiveChart.ChartGroups(1).GapWidth = 10
    With ActiveChart.Axes(xlCategory).TickLabels.Font
        .Bold = msoFalse
        .Size = 8
    End With
    Set cht_Series = cht.Chart.SeriesCollection(1)
    cht_Series.Format.Fill.ForeColor.RGB = RGB(255, 255, 255)
    Set cht_Series = cht.Chart.SeriesCollection(2)
    cht_Series.Format.Fill.ForeColor.RGB = RGB(134, 188, 37)

Thanks!

Upvotes: 1

Views: 511

Answers (2)

www.MSO-dlx.com
www.MSO-dlx.com

Reputation: 1

You need to set series.HasDataLabels = true and series.datalabels.Position = xlLabelPositionCenter (or very similar, can't check with Excel).

Upvotes: 0

m e
m e

Reputation: 291

To get what you want, you would need to add this line at the end of your code:

ActiveChart.SeriesCollection(2).HasDataLabels = True

Since you have defined a seriescollection object, this would also work:

cht_Series.HasDataLabels = True

Upvotes: 3

Related Questions