ACC2: How to Use OLE Automation to Modify MS Graph Object

• Microsoft Access version 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to use OLE Automation to modify a Microsoft Graph version 5.0 graph's title and hide or show the graph's legend.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

Changing a Graph's Title

The following example demonstrates how to use OLE Automation to modify a Graph 5.0 graph's title.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Open the sample database NWIND.MDB.

2. Open the Sales By Product form in Design view.

3. Set the form's DefaultEditing property to Allow Edits.

4. Add the following controls to the form:

         Text Box
         -------------
         Name: MyTitle
   

         Command Button
         ---------------------
         Name: Title
         Caption: Show Caption
   

5. Set the Show Caption command button's OnClick property to the following event procedure.

   NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

         Sub Title_Click ()
         Dim The_title As String
   
         '--------------------------------------------------------------
         ' This function reads the text box. If it is null or blank, it
         ' supplies a generic title, otherwise it adds a title and sets
         ' the caption to the text box's contents.
         '--------------------------------------------------------------
   
         If IsNull(Me![MyTitle]) Or Me![MyTitle] = "" Then
             The_title = "No Title"
         Else
             The_title = Me![MyTitle]
         End If
   
         If Me![Title].Caption = "Show Title" Then
             Me![Embedded13].Object.Application.Chart.Hastitle = -1
   
         Me![Embedded13].Object.Application.Chart.Charttitle.Caption = _
             The_title
             Me![Title].Caption = "Hide Title"
         Else
             Me![Embedded13].Object.Application.Chart.Hastitle = 0
             Me![Title].Caption = "Show Title"
         End If
   
         End Sub
   
   

6. Add the following line to the Declarations section of the Sales By Product form's form module:

         Option Explicit
   

7. View the form in Form view. Type any text in the text box you created in step 4, and then click the Show Caption button. The graph's title will change to the text box's text.

Hiding or Showing a Graph's Legend

The following example demonstrates how to hide or show a Graph 5.0 graph's legend.

CAUTION: Following the steps in this example will modify the sample database NWIND.MDB. You may want to back up the NWIND.MDB file, or perform these steps on a copy of the NWIND database.

1. Open the sample database NWIND.MDB.

2. Open the Sales By Product form in Design view.

3. Add a command button with the following properties to the form:

         Name: Legend
         Caption: Show Legend
   

4. Set the Show Legend command button's OnClick property to the following event procedure:

         Sub Legend_Click ()
   
         '----------------------------------------------------------
         ' If the legend is not present, show it, otherwise hide it.
         '----------------------------------------------------------
   
         If Me![Legend].Caption = "Show Legend" Then
            Me![Embedded13].Object.Application.Chart.Haslegend = -1
            Me![Legend].Caption = "Hide Legend "
         Else
            Me![Embedded13].Object.Application.Chart.Haslegend = 0
            Me![Legend].Caption = "Show Legend"
         End If
         End Sub
   
   

5. Add the following line to the Declarations section of the Sales By Product form's form module:

         Option Explicit
   

6. View the form in Form view. Click the Show Legend button to toggle between hiding and showing the form's legend.

REFERENCES

Microsoft Access "Building Applications," version 2.0, Chapter 8, "Events," pages 183-201, and Chapter 13, "Communicating with Other Applications," pages 281-311

For more information about OLE Automation, search for "OLE Automation," and then "Interoperability with Microsoft Word and Microsoft Excel (Common Questions" using the Microsoft Access Help menu.