dynamically modify chart title

[Guest]

Does anyone know how to dynamically modify a chart title in VBA to use an
active data field or an inputBox reply?

I'm charting daily statistics but I can't find a way to include the file
date in the chart title?

I'd appreciate any help with this.

 

[Duane Hookom]

Can't you just place a text box on the report to use as the chart title?

 

[Guest]

Thanks for the reply Duane.

What I am trying to do is to "automate" the creation of an "imbedded" chart
into a report.

Of course, I can use the Report title also as the chart title, but I'm
trying to do a lot more. E.g. Choose the type of chart, select the
datafields, choose colors, etc.

It it were in Excel I could record a macro convert it to VBA and go from
there, But I don't know how to duplicate that process in Access.

If you create a new report and insert an "Object" into the detail section,
where that Object is a "new" Microsoft Graph Chart, you'll better understand
what I'm attempting.

Can you assist in this process?

Thanks,
Lee

 

[Duane Hookom]

I have used the macro recorder in Excel and then copied the code into a form
or report module. This required some modification but it was minimal.

 

[Dick Haynes]

In Code Environment Create a reference to Microsoft Graph 9.0 Object
Library

The trick then is to get a strongly-typed Chart object which exposes
all the properties , including 'ChartTitle'

In the code example below I have a form called 'Whatever' which has
been built using the Form / New / Chart Wizard.
It has one control called chtOutstanding which is the chart object
(actually an Unbound Object Frame)


' ###############################################
Docmd.openForm "Whatever"
Dim frmChart as Form_Whatever
Set frmChart = Forms("Whatever")

' Changing the row source can be done directly
' with the Unbound Object Frame
' --------------------------
frmChart.chtOutstanding.RowSource = "s_04_GRAPH_ForOneMonth"


' Change Title, Legend , DataTable, etc, etc has to be done via
the MSGraph object library
' In Code Environment Create a reference to Microsoft Graph 9.0
Object Library which exposes an application an object library called
'Graph'
' ----------------------------------------
Dim cht As Graph.Chart
Set cht = frmChart.chtOutstanding.Object.Application.Chart
cht.HasTitle = True
cht.ChartTitle.Text =
Format(CVDate(Fn.Value("OutstandingView_TheBOMDate")), "mmmm yyyy")
cht.ChartTitle.Font.Size = 14
cht.SeriesCollection(1).Points(1).Interior.Color = QBColor(2)
' ###############################################

I hope this helps
Dick