VBA for Excel 2007 tutorial-pie chart data labels


Pie chart data labels

In the previous page, you learnt to create a simple pie chart without data labels. In this VBA code example, you will learn VBA code to display the data labels of the pie chart. This can be done by using the ApplyDataLabels() method of the chart object.  The ApplyDataLabels() method has the following prototype:

ApplyDataLabels(Type,Legend_Key, Header_Lines, Series_Name, Category_Name, Value, Percentage, Bubble_Size, Separator)

-The Type argument represents the type of data label. It can be xlDataLabelsShowLabel, xlDataLabelsShowNone, xlDataLabelsShowValue, xlDataLabelsShowPercent,etc.
-The Legend_Key accepts True or False. If True is supplied, the legend key will be showed next to the data points.
-The Leader_Lines argument represents the header lines of the data labels. It can take True or False. True if the header lines of data labels are shown.
-The Series_Name takes True or False. It displayed the names of data series, if True.
-The Category_Name displays the names of data categories if True.
-The Value argument represents the values of the data labels. It can be True or False.
-The Percentage is used when you want to display the percentages of data categories. It can take True or False.
-The Bubble_size also takes True or False. It represents the bubble size of the data label.
-The Separator specifies the separator for the data labels

The first argument is required. However, the rest are optional.


VBA for Excel: VBA example code to show data labels of pie chart

To run this VBA example code, you can copy and paste the VBA code below in to the block of Worksheet_Activate() procedure. To open this block, double-click the current worksheet, then in the objects dropdown list select Sheet1 and in events list select Activate.



VBA code for this example:



Option Explicit
Private Sub Worksheet_Activate()
 
    'Select the data source
    Range("A1:G2").Select
    'Create a clusted column chart
    ActiveSheet.Shapes.AddChart , 10, 100
    ActiveSheet.Shapes(1).Select
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$G$2"), PlotBy:=xlRows
    'Specify Pie chart type
    ActiveChart.ChartType = xl3DPie

    'Show data labels on the chart
    ActiveChart.ApplyDataLabels xlDataLabelsShowValue
 
End Sub 

Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.