Controlling colour of pie chart slices

I'm using a microsoft graph object to display a pie chart on a form to represent the proportion of red amber and green and black indicators in a table.  The red slice needs to be coloured red, the green slice needs to be green etc... The problem is getting the slices to appear as the right colours.  Either they come up as different colours when the form is loaded on different occaisions, or the displayed colours have no bearing to the RGB colour value that is being specified.   The code snippet below, which is run on the current event (have tried load activate etc) shows the technique I'm trying to use, basically display the data labels, identify the text of the label, apply the corresponding colour and then hide the labels again... sounds easy doesn't it ?
The technique I'm trying at the moment is to define the colour palette and then use the colorindex property to specify the required colour.  However this doesn't work at all.

My Previous attempt used the .Interior.Color property of the point object and this just about worked when I was using raw RGB colours e.g. RGB(255,0,0) for red etc, however even this didn't always work i.e. when the routine was first run the colours where wrong and it had to be run twice for the colours to be corrected.  In any case these colours are too intense, so I defined my own colours
Public Const kpiRed = 8947967
Public Const kpiGreen = 10678678
Public Const kpiAmber = 7972351
However when these were applied to the .Interior.Color the results were complete rubbish, so I'm wondering if there is a better way ...

Sub SetPie()
Dim x As Long
Dim p As Graph.Point
 
Me.Graph3.Object.Refresh
Me.Graph3.Object.Colors(1) = kpiRed
Me.Graph3.Object.Colors(2) = kpiGreen
Me.Graph3.Object.Colors(3) = kpiAmber
Me.Graph3.Object.Colors(4) = 0
 
DoEvents
 
For x = 1 To Me.Graph3.SeriesCollection.Count
 
    For Each p In Me.Graph3.SeriesCollection(x).Points
        p.ApplyDataLabels ShowCategoryName:=True
        'MsgBox p.DataLabel.Text
        Select Case p.DataLabel.Text
        Case "red"
            'p.Interior.Color = kpiRed
            'p.Interior.Color = 255
            p.Interior.ColorIndex = 1
        Case "amber"
            p.Interior.ColorIndex = 3
        Case "green"
            p.Interior.ColorIndex = 2
        Case "black"
            p.Interior.ColorIndex = 4
        End Select
            
        p.HasDataLabel = False
    DoEvents
    Next
    
 
Next
 
 
End Sub

Open in new window

LVL 17
NatchiketAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try using the qbcolor function

p.Interior.Color =qbcolor(4)    'red


The color argument has these settings:

Number Color Number Color
0 Black 8 Gray
1 Blue 9 Light Blue
2 Green 10 Light Green
3 Cyan 11 Light Cyan
4 Red 12 Light Red
5 Magenta 13 Light Magenta
6 Yellow 14 Light Yellow
7 White 15 Bright White
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NatchiketAuthor Commented:
hmmm well it kind of works (like the RGB function) although when the form first opens one of the colours (black) is wrong, although it comes right when I re run the function.  At least qbcolor allows the colours to be a bit  muted which is an improvement ...
0
NatchiketAuthor Commented:
ok slight progress.  I've set the timer interval to 500
the following code corrects the graph before anyone's had a good time to look at it

Private Sub Form_Timer()
SetPie
Me.TimerInterval = 0
End Sub

Although I'm surprised it allows the interval to be set back to 0 but seems to work
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

NatchiketAuthor Commented:
Interestingly an initial timer interval much less than 500, e.g. 100, 200 etc doesn't seem to work
0
Jeffrey CoachmanMIS LiasonCommented:
Why not use the chart Wizard in Access?

It always keeps the same color scheme.

JeffCoachman
0
NatchiketAuthor Commented:
Hi Jeff it may keep the same colour scheme, but the colours it allocates to pie chart points will vary according to wether the value data exists or not, etc..  To test this create a simple one column table with a text field and enter the values Red, Red, Green, Blue etc .  If you then create a pie chart using the wizard, with the slice size depending on the count of each colour and get it to colour the points according their names, it looks great.  However if you then start modifying the data so that all the reds are replaced by green etc, you'll soon find that it switches the slice colours ... not what I'm after.
0
Jeffrey CoachmanMIS LiasonCommented:
Oh,

OK, I misunderstood.

Jeff
0
Jeffrey CoachmanMIS LiasonCommented:
Natchiket,

Thanks buddy, but you know me, ..if my post did not explicitly help you out then you really don't have to give me any points.

If you want to change this, and give Cap all the points, I have no problem.
;-)

Enjoy your weekend.
;-)

Jeff
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.