Control form pivot chart in a report.

[Jesper]

Hi All,

I have a bar pivot chart made in a form this load fine, I have the below VBA code in place to control the bar color depending on what the bar show.

Set frms = Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0)

Dim i As Long
With frms
For i = 0 To Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection.Count - 1
With .SeriesCollection(i).Name
Select Case Forms![OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection(i).Name
Case "chat"
frms.SeriesCollection(i).Interior.Color = RGB(92, 131, 180)
Case "Email"
frms.SeriesCollection(i).Interior.Color = RGB(192, 80, 77)
Case "Legal"
frms.SeriesCollection(i).Interior.Color = RGB(157, 187, 97)
Case "Letter"
frms.SeriesCollection(i).Interior.Color = RGB(128, 102, 160)
Case "Mailbox"
frms.SeriesCollection(i).Interior.Color = RGB(75, 172, 198)
Case "Other"
frms.SeriesCollection(i).Interior.Color = RGB(245, 157, 86)
Case "Phone"
frms.SeriesCollection(i).Interior.Color = RGB(64, 92, 126)
Case "RMA Return"
frms.SeriesCollection(i).Interior.Color = RGB(135, 56, 54)
End Select
End With
Next i
End With

When I then open the Report this is not working at all each bar just get a random color regardless what the bar show.

Anyone out there that would know how to convert the above to fit a Pivot chart form a form that is displayed on a report.

Kind regards,

Jesper Jespersen

 

[Jesper]

Hi All

For any one else that needs some thing like this, then I have just found the solution:

Private Sub Report_Load()

Set frms = Me.OtherCases_created_by_Indv_by_Month_chart.Form.ChartSpace.Charts(0)

Dim i As Long
With frms
For i = 0 To Me.[OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection.Count - 1
With .SeriesCollection(i).Name
Select Case Me.[OtherCases created by Indv by Month chart].Form.ChartSpace.Charts(0).SeriesCollection(i).Name
Case "chat"
frms.SeriesCollection(i).Interior.Color = RGB(92, 131, 180)
Case "Email"
frms.SeriesCollection(i).Interior.Color = RGB(192, 80, 77)
Case "Legal"
frms.SeriesCollection(i).Interior.Color = RGB(157, 187, 97)
Case "Letter"
frms.SeriesCollection(i).Interior.Color = RGB(128, 102, 160)
Case "Mailbox"
frms.SeriesCollection(i).Interior.Color = RGB(75, 172, 198)
Case "Other"
frms.SeriesCollection(i).Interior.Color = RGB(245, 157, 86)
Case "Phone"
frms.SeriesCollection(i).Interior.Color = RGB(64, 92, 126)
Case "RMA Return"
frms.SeriesCollection(i).Interior.Color = RGB(135, 56, 54)
End Select
End With
Next i
End With

End Sub

Kind regards

Jesper Jespersen