Using VBA to Dynamically Format a MS Graph in a Report 2

[jaaret]

I am attempting to change the color of a column in a MS Graph through VBA. I cut and pasted this code (simplifed) into the Report_Open subroutine but it errors on the first line:


ActiveChart.SeriesCollection(1).Select
With Selection.Interior
.ColorIndex = 255
.Pattern = xlSolid
End With

I've tried changing the first line to:

Me.Graph14.SeriesCollection(1).Select

This also errors. How can I modify this code to make it work in Access?

Jaaret

 

[dhookom]

There is no way to select anything in a report display. I'm not sure what type of chart you are displaying but this might work. It didn't for a series displayed as a vertical bar.

 Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim chrt As Object
    Set chrt = Me.chart1
    With chrt.SeriesCollection(1)
        .ColorIndex = 255
        .Pattern = Solid
    End With
    Set chrt = Nothing
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[jaaret]

I'm displaying a vertical bar chart.

Your code errors on the line:

With chrt.SeriesCollection(1)

I opened up the graph in design view and with the graph bars highlighted the tool tip while pointing to a bar reads, Series "CountofHomes" so I tried

With chrt.SeriesCollection("CountofHome")

which, of course, errored.

Other suggestions?

 

[dhookom]

I would try early bind by setting a reference to the MS Chart/Graph. Then you can dim chrt as a Chart or whatever. This might help with the syntax.

Duane
Hook'D on Access
MS Access MVP