Updating Chart Title With VB 1

[billschu]

Hi, I'm trying to figure out how to edit the title of a chart through Visual Basic. The chart is embedded in a form. When the chart is first created it adds a default title such as "Chart1". The only way I can edit it then is to do it manually. I can't find the appropriate property of the chart to change it.

Thanks,
Bill

 

[VBslammer]

The secret here it to reference the right library. From the IDE, click [tt]Tools-->References[/tt] then check the 'Microsoft Graph 9.0 Object Libary'

With that done, the only other tricky part is dimensioning your variable to the object type that matches your chart. Here's an example:

[tt]

Private Sub cmdChangeTitle_Click()

Dim g As Graph.Chart <--tricky declaration
Set g = Me!Chart1.Object <--must refernce the object

With g
.ChartTitle.Text = txtTitle
End With

End Sub
[/tt]

That's it, have fun...

VBSlammer

Unemployed in Houston, Texas

 

[billschu]

Thanks... I'm having trouble accessing the rowsource property of the chart. Any ideas?

Bill

 

[AlanDI1]

VBSlammer that is great information and has pointed me in the right direction with a problem I am having. BUT (there is always a but) I get &quot;Unable to set the ... property of ...&quot; when I try this. I tried &quot;text' and 'caption' properties. Any ideas? As BillMe asked, will this general principle for RowSource also?

Thanks for your great information.

 

[VBslammer]

Howdy,

The RowSource property is exposed through Access, so you can reference it without using the explicit reference to the ActiveX object:

  Me!Chart1.RowSource = "TRANSFORM Count(*) AS [Count] SELECT [Part] FROM [tblPartPricing]   GROUP BY [Part] PIVOT [Price];"

The explicit reference to &quot;Object&quot; is only needed when accessing methods or properties not exposed by Access.

VBSlammer
Unemployed in Houston, Texas

 

[AlanDI1]

Thanks for your response.

When I try this I get an error message the says &quot;You Entered and expression that has an invalid reference to the property RowSource&quot;. If I click Debug and then continue it will run (and actually use the correct RowSource). Short of trapping that error in my error routine and just continuing when it appears is there a better solution?

I am using Access 2000 in Windows XP Pro, does that have any effect?

Also, still not sure what causes &quot;Unable to set the Text property of ChartTitle&quot; in previous example.

Thanks again.

 

[VBslammer]

Error 1004 - &quot;Unable to set the Text property of the ChartTitle class&quot; is most likely happening because your chart does not have a chart title, since you can only modify the title property via code if one already exists on the chart.



VBSlammer

Unemployed in Houston, Texas

 

[AlanDI1]

VBSlammer you are truely THE MAN. Thank you very much. The chart title, Axis titles, etc. work beautifully.

I still have issues with the rowsource but I have gone at it a different way.

Thank you once again.

 

[sfatz]

Thanks VBSlammer for the great help. I am getting a different error than the others that perhaps you can help with.

On the line ...
Set g = Me!Chart1.Object
... I am getting "Error 2771, The bound or unbound object frame you tried to edit doesn't contain an OLE object."

Checking the properties of the object shows that the OLE Class = 'Microsoft Graph Chart' and Class = "MSGraph.Chart.8".

Do you know what's causing the error message?

Thanks in advance for any help that you can provide.

 

[VBslammer]

It could be that there are no records returned for the rowsource query you supplied, which will not allow the chart to render. You could add an error handler to handle it like this:

Private Sub Form_Load()
On Error GoTo ErrHandler
  [green]'load code[/green]
ExitHere:
  Exit Sub
ErrHandler:
  If Err = 2771 Then
    MsgBox "No records matched your criteria."
  Else
    MsgBox Err & " - " & Err.Description
  End If
  Resume ExitHere
End Sub

VBSlammer

Unemployed in Houston, Texas

 

[VBslammer]

Previous post was just a guess, since I can't reproduce this error at all on my system. Can you right-click on the chart in design mode and open it for editing?

My OLE Class is: Microsoft Graph 2000 Chart
and the Class is: MSGraph.Chart.8

Also, did you put your code in a button-click event as I did or is it in a different event?

VBSlammer

Unemployed in Houston, Texas

 

[sfatz]

I think I have the solution.

I had the code, including error checking, in the Form's Load event. However, some additional research last night led me to believe that some changes can't be done unless the chart is opened in design mode.

So I moved the code to a module. From there, I opened the report in design mode first, made the changes, closed the report, and then reopened the report as usual. I didn't get the error this way, and the report seemed to reflect the changes that I wanted. I was too tired by that time to check the data, but I suspect that that is okay as well.

I found it interesting during this "excercise" that there is woefully little about the Graph.Chart on the web. I guess that not too many people use it (or, worse, that I'm too dumb to use it porperly - sigh)

Anyway, Thanks a bunch for your efforts.

P.S. "Unemployed" is only a state of mind. ;-)

 

[VBslammer]

I too found little on the Web for the Graph.Chart object and much of what I learn is from trial and error. I didn't realize you were manipulating a Report - my example used a form which is much more flexible than a report. I have seen other posts concerning charts/reports that had the exact same solution you mention, ie opening the report in design mode to make the changes.

Happy coding!

VBSlammer

Unemployed in Houston, Texas