CODEWITHSUNDEEP
excelvbacharts
Ana DEV
Ana DEV

Reputation: 1030

How can I add chart data labels with percentage?

I want to add chart data labels with percentage by default with Excel VBA. Here is my code for creating the chart:

Private Sub CommandButton2_Click()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet1'!$A$6:$D$6")
ActiveChart.ChartType = xlDoughnut
End Sub

It only creates Doughnut chart with no information labels.

Also, when I want to create another chart type with this same information how can I change the coordinates of the chart so it will not appear look like it overwrites the same one?

Upvotes: 2

Views: 1771

Answers (1)

manu
manu

Reputation: 942

Here is one way to have Data Label in %:

            Private Sub CommandButton2_Click()
Dim Cell As Range
    Set Cell = ActiveCell
            Set Myrange = Sheets("Sheet1").Range("$A$6:$D$6")

            ActiveSheet.Shapes.AddChart.Select
            ActiveChart.SetSourceData Source:=Myrange
            ActiveChart.ChartType = xlDoughnut

            With PlotArea
                ActiveChart.ApplyLayout (6)
            End With

            With ActiveChart
                .Legend.Delete
                '.ChartTitle.Delete
                '.ChartTitle.Text = "Here goes your tittle"
            End With

        With ActiveChart.SeriesCollection(1)
            .Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            .Points(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            .Points(3).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
            .Points(4).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
        End With

    With ActiveChart.Parent
             .Height = 200 ' resize
             .Width = 300  ' resize
             .Top = Cell.Top    ' reposition
             .Left = Cell.Left  ' reposition
         End With

            End Sub

Second Type of Graph:

Private Sub CommandButton2_Click()
    Set MyRange = Sheets("Sheet1").Range("$A$6:$D$6")

    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=MyRange
    ActiveChart.ChartType = xlColumnClustered

    With PlotArea
        ActiveChart.ApplyLayout (2)
    End With

    With ActiveChart
        .Legend.Delete
        '.ChartTitle.Delete
        '.ChartTitle.Text = "Here goes your tittle"
    End With

With ActiveChart.SeriesCollection(1)
    .Points(1).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
    .Points(2).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
    .Points(3).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
    .Points(4).Format.Fill.ForeColor.RGB = RGB(255, 0, 0)
End With

With ActiveChart.Parent
     .Height = 200 ' resize
     .Width = 300  ' resize
     .Top = 300    ' reposition
     .Left = 300   ' reposition
 End With

End Sub

And here you can find the color code: http://dmcritchie.mvps.org/excel/colors.htm

Upvotes: 1

Related Questions