Bar Chart

[JSmith]

I have an access bar chart that the color keeps changing on the bars everytime i open it.
Is there some way in VBA I can set the bar color.
Example: RC1 bar will be red one time and yellow the next time i open it.

Thanks,

JSmith

 

[vbaInet]

Not really played around with Bar Charts but I believe you can alternate the colour using an IF statement in the form's Load event. Pseudo:

If myBarObject.Backcolor = vbRed Then
    myBarObject.Backcolor = vbGreen
Else
    myBarObject.Backcolor = vbRed
End If

 

[JSmith]

The myBarObject.Backcolor = vbRed works well to set the back color of the chart; however, i am having an issue with selecting a certain bar, such a bar that draws the data for ford, another will draw data for chevy, and etc. the issue i am having is figuring out how to set bar color ford to vbred.

 

[ChrisO]

If you post a demo of the chart, in Access2003, we can do a better job of suggesting something.

 

[vbaInet]

If you post a demo of the chart, in Access2003, we can do a better job of suggesting something.

I've noticed Chris loves his charts

 

[ChrisO]

Chris also likes banging his head on a wall…

 

[vbaInet]

Hehe!! Not with all that bright and sunny weather in Australia, we're suffering here lol

 

[JSmith]

I tried to insert a image; however, it wanted a url, so basically i could not figure out how to attach it. Anyway maybe i can describe it better. The chart is a stack chart. The number of items in a stack can flux, and not every stack has the same number of items. Example: in stack one it can have (A1 color red, B2 color blue, B3 color green , C1 color yellow) stack two can have (B1 color white, B3 color green , C1 color yellow) and etc...
However, as i mentioned the stacks can change everytime i run the chart. Next time the stack one can be (A2 color red, A1 color blue, B3 color green , C1 color yellow). As you can see from the example one time A1 is red and another time A1 is blue. I would like to be able to define in code that A1 always be red, A2 always be blue, and etc..

I have tried changing them in design view however, because the stack chart is different everytime it does not hold.

thanks for the help,

jsmith

 

[ChrisO]

This is one method that seems to work: -

Public Sub SetSeriesColour()
    Dim vntSeriesColour As Variant

    Me.chtTestChart.Requery
    
    [color=green]' Wait for the Chart to finish processing, it can be slow.[/color]
    Do Until Me.chtTestChart.SeriesCollection.Count = DCount("*", Me.chtTestChart.RowSource)
        DoEvents
        Sleep 1
    Loop
    
    With CurrentDb.OpenRecordset(Me.chtTestChart.RowSource)
        Do Until .EOF
            vntSeriesColour = DLookup("SeriesColour", "tblChartSeriesColours", "SeriesName = " & Chr$(34) & !SeriesName & Chr$(34))
            Me.chtTestChart.SeriesCollection(.AbsolutePosition + 1).Interior.Color = vntSeriesColour
            .MoveNext
        Loop
        .Close
    End With

End Sub

Store all the Series Names and Colours in Table tblChartSeriesColours and set them on the fly.

Test demo attached.

 

[JSmith]

Wow it looks great! I am actually working in a report, but many times things that work in forms will work in reports, so i am going to try an carry it over.

Thanks for all the help.

Jsmith