CODEWITHSUNDEEP
excelvba
Kevin P.
Kevin P.

Reputation: 1053

Add Data Labels with VBA

I have a Sub based off a checkbox that I want to essentially switch between showing data labels on a graph and hiding them. I recorded the macro and altered it to no longer say "Selection" or "ActiveChart" because I don't want to have the user select the chart. I think that's where my problem is because it does absolutely nothing.

I can't seem to narrow down the issue since it's a rather long chunk. Any help would be appreciated!

Sub Checkbox()
Dim Cht As ChartObject
Set Cht = Sheet5.ChartObjects("Chart 12")
    If Sheet1.Range("W11") = "TRUE" Then
    Cht.Chart.SetElement (msoElementDataLabelTop)
    Cht.Chart.ApplyDataLabels
    Cht.Chart.FullSeriesCollection(1).DataLabels.Select
    Cht.ShowCategoryName = True
    Cht.Separator = "" & Chr(13) & ""
    With Cht.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(127, 127, 127)
        .Solid
    End With
    With Cht.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    With Cht.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(68, 114, 196)
        .Transparency = 0.75
        .Solid
    End With
    Application.CommandBars("Format Object").Visible = False
    Else: Cht.Chart.SetElement (msoElementDataLabelNone)
    End If
End Sub

I was told a while ago it's better to reference a cell rather than a checkbox, hence the reason I have the code looking at W11 for "TRUE" or "FALSE". I would rather use the checkbox if it seems practical.

Upvotes: 0

Views: 10685

Answers (3)

Kevin P.
Kevin P.

Reputation: 1053

Thanks to help I was able to figure out the issue. Below is the final code:

Sub Checkbox()
    If Sheet1.Range("W11") = True Then
    Sheet5.Shapes("Chart 12").Chart.SetElement (msoElementDataLabelTop)
    Sheet5.Shapes("Chart 12").Chart.ApplyDataLabels
    Sheet5.Shapes("Chart 12").Chart.FullSeriesCollection(1).DataLabels.Select
    Selection.ShowCategoryName = True
    Selection.Separator = "" & Chr(13) & ""
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(127, 127, 127)
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = 0
        .Transparency = 0
        .Solid
    End With
    With Selection.Format.Fill
        .Visible = msoTrue
        .ForeColor.RGB = RGB(68, 114, 196)
        .Transparency = 0.75
        .Solid
    End With
    Application.CommandBars("Format Object").Visible = False
        Else
        Sheet5.Shapes("Chart 12").Chart.FullSeriesCollection(1).DataLabels.Select
        Sheet5.Shapes("Chart 12").Chart.SetElement (msoElementDataLabelNone)
    End If
End Sub

Upvotes: 0

Xabier
Xabier

Reputation: 7735

Try the following, I believe you were referencing the chart incorrectly, by using the Shapes object, you can get to your desired chart:

Sub Checkbox()
    If Sheet1.Range("W11") = True Then
        Sheet1.Shapes("Chart 1").Chart.SetElement (msoElementDataLabelShow)
        Sheet1.Shapes("Chart 1").Chart.ApplyDataLabels
        Sheet1.Shapes("Chart 1").Chart.FullSeriesCollection(1).DataLabels.Select
        Sheet1.Shapes("Chart 1").Chart.FullSeriesCollection(1).DataLabels.ShowCategoryName = True
        Sheet1.Shapes("Chart 1").Chart.FullSeriesCollection(1).DataLabels.Separator = "" & Chr(13) & ""
        With Sheet1.Shapes("Chart 1").Chart.FullSeriesCollection(1).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(127, 127, 127)
            .Solid
        End With
        With Sheet1.Shapes("Chart 1").Chart.FullSeriesCollection(1).Format.Fill
            .Visible = msoTrue
            .ForeColor.ObjectThemeColor = msoThemeColorAccent1
            .ForeColor.TintAndShade = 0
            .ForeColor.Brightness = 0
            .Transparency = 0
            .Solid
        End With
        With Sheet1.Shapes("Chart 1").Chart.FullSeriesCollection(2).Format.Fill
            .Visible = msoTrue
            .ForeColor.RGB = RGB(68, 114, 196)
            .Transparency = 0.75
            .Solid
        End With
        Application.CommandBars("Format Object").Visible = False
    Else
        Sheet1.Shapes("Chart 1").Chart.SetElement (msoElementDataLabelNone)
    End If
End Sub

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

The issue is that 

If Sheet1.Range("W11") = "TRUE"

is not true.

If you Debug.Print Sheet1.Range("W11") = "TRUE" it will return false instead of the expected true. The cause is that Sheet1.Range("W11") returns a boolean not a string in this case.

You can verify the return type with

Debug.Print VarType(Sheet1.Range("W11"))

which returns 11 and that means boolean (see VarType Function).

So you should change it to

If Sheet1.Range("W11") = True

which is true if the cell W11 contains TRUE.

Upvotes: 1

Related Questions