ACC2000: How to Programmatically Position Data Labels in Microsoft Graph (230061)

SUMMARY

When you create a chart by using the Microsoft Access Chart Wizard, you do not have the option to place data labels on the chart. You can, however, add data labels to the chart manually by using Microsoft Graph, or you can add them programmatically by writing custom Visual Basic for Applications code.

This article shows you how to programmatically add and position data labels on a Microsoft Graph chart.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

MORE INFORMATION

To use Visual Basic for Applications code to add and position data labels on the chart, follow these steps.

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.
Open the sample database Northwind.mdb.
On the View menu, point to Database Objects, and then click Forms.
Click New.
In the New Form dialog box, click Chart Wizard, click Invoices in the drop-down list, and then click OK.
Add the SalesPerson and OrderDate fields to the Fields for Chart list, and then click Next.
Click Area chart (the first button on the third row of buttons), and then click Next. Note that the Chart Wizard automatically places SalesPerson on the horizontal axis and OrderDate by month on the vertical axis.
Double-click OrderDate by month to display the Group dialog box, where you can select how you want the OrderDate field grouped.
Click Year, and then click OK.
Click Finish.
On the View menu, click Design View to open the form in Design view.
Click once on the chart object to select it.
On the View menu, click Properties to display the property sheet for the chart object.

Set the following properties for the chart:

   Unbound Object Frame
   --------------------
     Name: Chart
     Left: .25"
      Top: .25"
    Width: 4"
   Height: 3"

On the View menu, click Form Header/Footer to display the form header section.
Add a command button to the form header section.

Set the following properties for the command button:

   Command Button
   ----------------------
      Name: cmdAddLabels
   Caption: Add Labels

On the View menu, click Code to view the module of the form.
On the Tools menu, click References.
In the References dialog box, click to select the Microsoft Graph 9.0 Object Library check box, and then click OK.

Add the following code to the module:

Private Sub cmdAddLabels_Click()
    Dim cht As Graph.Chart
    Dim chtSeries As Graph.Series
    Dim chtLabel As Graph.DataLabel
    Set cht = Me.Chart.Object
    For Each chtSeries In cht.SeriesCollection
       'Enable Data Labels in the chart
       chtSeries.HasDataLabels = True

       'Loop through each data label and set its
       'Top, Left, and Font properties
       For Each chtLabel In chtSeries.DataLabels
          chtLabel.Top = chtLabel.Top - 10
          chtLabel.Left = chtLabel.Left + 10
          chtLabel.Font.Size = 8
       Next
    Next
End Sub

On the File menu, click Close and Return to Microsoft Access.
On the View menu, click Form View. Note that the chart is displayed but that it does not show any labels.
Click the command button.

Note that data labels appear in the chart.