ACC2000: How to Programmatically Position Data Labels in Microsoft Graph

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.

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

1. 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.
   
   
2. Open the sample database Northwind.mdb.
3. On the View menu, point to Database Objects, and then click Forms.
4. Click New.
5. In the New Form dialog box, click Chart Wizard, click Invoices in the drop-down list, and then click OK.
6. Add the SalesPerson and OrderDate fields to the Fields for Chart list, and then click Next.
7. 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.
8. Double-click OrderDate by month to display the Group dialog box, where you can select how you want the OrderDate field grouped.
9. Click Year, and then click OK.
10. Click Finish.
11. On the View menu, click Design View to open the form in Design view.
12. Click once on the chart object to select it.
13. On the View menu, click Properties to display the property sheet for the chart object.
14. Set the following properties for the chart:

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

15. On the View menu, click Form Header/Footer to display the form header section.
16. Add a command button to the form header section.
17. Set the following properties for the command button:

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

18. On the View menu, click Code to view the module of the form.
19. On the Tools menu, click References.
20. In the References dialog box, click to select the Microsoft Graph 9.0 Object Library check box, and then click OK.
21. 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
    					

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

Note that data labels appear in the chart.