excel - 如何添加带有百分比的图表数据标签？

Question

我想使用 Excel VBA 默认添加带有百分比的图表数据标签。这是我创建图表的代码:

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

它只创建没有信息标签的圆环图。

另外，当我想使用相同的信息创建另一种图表类型时，如何更改图表的坐标，使其看起来不会覆盖同一个图表？

Answer 1

这是在 % 中使用数据标签的一种方法:

            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

第二种图表:

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

在这里您可以找到颜色代码:
http://dmcritchie.mvps.org/excel/colors.htm