I have inserted a chart into the header of a report. I used the chart wizard to bind the chart to a table. I would like to now modify the chart programatically. For example, I would like to change the legend text and t he chart title. How do I get access to the chart's properties? I have added a reference to the Microsoft Graph 10.0 Object Library. Thanks In advance-Steve

With the Microsoft Graph x.0 Object Library referenced, open the Object Browser (F2), select 'Graph' from the libraries drop-down list, select the Chart class, and all the Chart object's properties and methods are dislayed. Selecting one and clicking the Help button will take you to the Help file with code listings.

Doesn't look like the legend text can be gotten at programmatically, but the chart title is easy:

' In report module
Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)

    ' Change chart title
    Me.TestGraph.ChartTitle.Text = "New Title"

    ' Format legend text
    Me.TestGraph.Legend.Font.ColorIndex = 5
    Me.TestGraph.Legend.Font.Bold = True

End Sub
HTH,

Bob

Thanks for response. I tried your suggestion but no luck. The problem is that when I type "me.graph1." and the intellisense list of items appears, legend, chart title and other things I want to manipulate aren't on the list. I think some other object declarations are necessary. That is what I am trying to find out. Once I figure out how to do this I think I can get to the legend text through the Series properties. I think what appears in the legend is the series name.

I had been putting my code in the form_open Sub. Here I get an error, 'Object doesn't support this property or method'. When I tried your suggestion and put it in the PageHeaderSection_Print the code worked! I got a new title. However, the intellisense doesn't work and ChartTitle is not on the dropdown when I put the dot in after "Me.Graph1." When I experimented by trying
"Me.Graph1.SeriesCollection(0).Caption="Someth ing" I get "Unable to get to the Series Collection property of the Chart Class" . I know I am missing something here.

Intellisense doesn't list the properties and methods in question, but the code runs nonetheless. Did you paste it in and run it? The syntax is straight from the Help file. The only reference I added to my default references is the Microsoft Graph x.0 Object Library. Ran fine.

Intellisense may be listing members of some default container control for the graph object.

Bob

You are correct. The code works. I tried me.Graph1.HasLegend=vbTrue and didn't get an error message. I guess now I just need to know how to get at the legend text. I think it night be through the seriescolection but haven't had any luck. - Steve

Hi Steve,

Glad what you have so far is working. Regarding:

To get the Chart object's members listed in Intellisense, create a new instance of the Chart class, assign it a reference to the Chart control on your report, and then work with the object variable, like:

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)

    Dim objChart As Chart
    Set objChart = Me.TestGraph

    ' Change chart title
    objChart.ChartTitle.Text = "New Title"

    ' Format legend text
    objChart.Legend.Font.ColorIndex = 5
    objChart.Legend.Font.Bold = True

End Sub

HTH,

Bob

Dern, should've run that first.

Change:

Dim objChart As Chart

to:

Dim objChart As Object

and you should be good to go.

Bob

Oops, lost Intellisense. There's gotta be a way to do this in VBA. Let me play with it for a bit.

Bob

Few, got it:

Private Sub PageHeaderSection_Print(Cancel As Integer, PrintCount As Integer)

    Dim objChart As Chart
    Set objChart = Me.TestChart.Object.Application.Chart

    ' Change chart title
    objChart.ChartTitle.Text = "New Title"

    ' Format legend text
    objChart.Legend.Font.ColorIndex = 5
    objChart.Legend.Font.Bold = True

End Sub

This will give you Intellisense for the object variable's members.

My chart control is named "TestChart". This is the OLE object embedded in your report that serves as a container control for your Chart object (similar to the way Forms are embedded in SubForm container controls). "Chart" is the ActiveX object hosted by the OLE container control.

"Me.TestChart.Object.Application.Chart" returns a reference to the ActiveX control (the reference assigned to the objChart object variable)

HTH,

Bob