Changing a graphs color based on criteria

[McArthurGDM]

Hi,

I am creating a percentage chart in a report. The results do NOT display dynamically. That is, I have a field with values like:



1."Banana"
2."Orange"
3."Apple"
4."Kiwi"

And each time the graph displays it may show only 1. and 2., or 3. and 4., or maybe 1. and 2. and 3..

If I assign colors in this order:

Apple > RED
Banana > YELLOW
Kiwi > GREEN
Orange > ORANGE

Then, say, only "Banana" and "Kiwi" shows up in the report, then what displays is:

Banana > Red
Kiwi > Yellow

As you can see, it shifts the assignment of the colors based on order, not on the actual field value.

Is there a way to get it so that the field value determines the color that is chosen in the graph report?

Thanks!

[June7]

Review https://www.accessforums.net/program...vba-29178.html

[McArthurGDM]

I tried it. I still get the same results. I'm wondering if this type of graph isn't what the code entails you should have. Any thoughts?

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)


Dim chtObj As Object
Dim j As Integer
Dim strType As String
Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long, c5 As Long, c6 As Long


c1 = RGB(0, 0, 225)
c2 = RGB(0, 128, 0)
c3 = RGB(255, 255, 0)
c4 = RGB(255, 165, 0)
c5 = RGB(255, 0, 0)
c5 = RGB(255, 255, 255)


Set chtObj = Me.gphMPR.Object


For j = 1 To 6
    strType = chtObj.SeriesCollection(j).Points(1).DataLabel.Text
    Debug.Print strType
    chtObj.SeriesCollection(j).Points(1).Interior.Color = Switch(strType = "Complete", c1, strType = "In Process", c2, strType = "Potential Delay", c3, strType = "At Risk", c4, strType = "Past Due", c5, strType = "Not Started", c6)
Next


End Sub

[June7]

Don't know. Haven't done any more experimentation with this. I abandoned the effort described in my thread.

Your code doesn't set c6 variable, you repeat c5. However, that of course won't resolve the issue.

I analyze graph issues best when I can work with data and objects. If you want to provide db, follow instructions at bottom of post. No promises though. Not much out there on programmatically managing graph formatting.