CODEWITHSUNDEEP
excelvba
Jacob
Jacob

Reputation: 346

VBA AutoShapeType Ignored When Macro Runs

I have the following vba code:

Dim targetChart As Chart
Dim labelRange As Range

Set targetChart = Worksheets("Graph").ChartObjects("Chart 2").Chart
Set labelRange = Worksheets("Graph").Range("H3", "H" & lastrow)

With targetChart.SeriesCollection(4)
        .ApplyDataLabels
        With .DataLabels
            .Format.AutoShapeType = msoShapeRoundedRectangle
            .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, labelRange.Address(External:=True), 0
            .ShowCategoryName = False
            .ShowRange = True
            .ShowSeriesName = False
            .ShowValue = False
        End With
    End With

I want to format my data labels as rounded rectangles, but when I run the macro the .Format.AutoShapeType = msoShapeRoundedRectangle seems to not execute. When I step through the macro it doesn't throw an error so I am not sure what I am doing wrong. Everything else within the with statement works as intended.

The reason I have .ShowValue = False is because I am setting the data label values from a range.

Upvotes: 0

Views: 423

Answers (2)

cybernetic.nomad
cybernetic.nomad

Reputation: 6368

Try this:

targetChart.FullSeriesCollection(1).ApplyDataLabels
    With targetChart.FullSeriesCollection(1).DataLabels
    .ShowRange = True
    .ShowValue = False
    .Format.AutoShapeType = msoShapeRoundedRectangle
    .Format.TextFrame2.TextRange.InsertChartField msoChartFieldRange, labelRange.Address(External:=True), 0
    .Format.Fill.Visible = msoTrue
    .Format.Fill.ForeColor.ObjectThemeColor = msoThemeColorAccent2
    .Format.Fill.ForeColor.TintAndShade = 0.5
    .Format.Fill.ForeColor.Brightness = 0
    .Format.Fill.Solid
End With

Upvotes: 2

xidgel
xidgel

Reputation: 3145

In Excel's Help it says that both DataLabels.Format (the entire DataLabels collection) and DataLabel.Format (a single DataLabel) are read-only. Maybe what you're trying can't be done.

Upvotes: 0

Related Questions