Results 1 to 12 of 12
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Setting columns in Graph to specific colours (Access 2003)

    I have a graph which shows TY and LY sales figures. Is there a way to set the colours of both TY and LY budget column to the same colour.

    I also require to do it with other columns as well.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting columns in Graph to specific colours (Access 2003)

    Do you have a fixed number of columns? If so, you should be able to set the color manually by double clicking the chart, then selecting the columns etc.
    If not, you may need to use VBA, but we'd need to have more info if you want help with that.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting columns in Graph to specific colours (Access 2003)

    The number of columns can vary depending on the users selections.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting columns in Graph to specific colours (Access 2003)

    See previous reply - I'd need to know details.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting columns in Graph to specific colours (Access 2003)

    The user selects up to 10 columns to be shown, from this i build the query. So the query will have a different number of columns each time the graph is produced. So there is no manual tuning of the graph, it is generated on the fly.
    I dont know what other details you require from me?

    Another question re graphs, is there a way to set the maximum value and increment for the vertical axis? There are differences between LY (last years) and TY (this years) figures, so i would like to show 2 graphs with the same vertical maximum value and increments.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting columns in Graph to specific colours (Access 2003)

    Microsoft has a sample database that demonstrates how to manipulate charts in VBA: Microsoft Access 97 Sample Graphs Available in Download Center. You can convert it to Access 2000 or 2002/2003 format.

    If you want specific help, please post a stripped down and zipped copy of your database.

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting columns in Graph to specific colours (Access 2003)

    Thanks form the link, i cannot give you the database, it's a clients database.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Setting columns in Graph to specific colours (

    Pat

    I have recently had to do something similar with Excel charts. I set up an array which is populated according to how many colours(intSeriesCount) are needed to be shown

    ReDim agintSeriesColour(intSeriesCount)
    Select Case intSeriesCount
    Case 1
    agintSeriesColour(0) = 17 'Bright green
    Case 2
    agintSeriesColour(0) = 17 'Bright green
    agintSeriesColour(1) = 18 'Yellow
    Case 3
    agintSeriesColour(0) = 19 'Dark green
    agintSeriesColour(1) = 17 'Bright green
    agintSeriesColour(2) = 18 'Yellow
    Case 4
    agintSeriesColour(0) = 19 'Dark green
    agintSeriesColour(1) = 17 'Bright green
    agintSeriesColour(2) = 18 'Yellow
    agintSeriesColour(3) = 20 'Light Orange
    ...
    End Select

    The chart has its colours set as follows :

    For intCount = 1 To XLcht.SeriesCollection.Count
    XLcht.SeriesCollection(intCount).Interior.ColorInd ex = agintSeriesColour(intCount - 1)
    Next

    You can set the min/max values of axes as follows :

    .Axes(xlValue, xlPrimary).MaximumScale = sglMaxLost
    .Axes(xlValue, xlPrimary).MinimumScale = 0

    HTH

    Nick

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting columns in Graph to specific colours (

    Thanks Nick, i will try it out and let you know.

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting columns in Graph to specific colours (

    What event does this code go into? Especially the code to set the Maximum scale.

    How do you address the vertical increment on the graph?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Setting columns in Graph to specific colours (

    > What event does this code go into?

    Depends on where you want to use it - for a chart on a form, you could use the On Current event, or the On Load event if the form itself is unbound.

    > How do you address the vertical increment on the graph?

    Depends on the chart - it's different for a bar chart than for a column chart, obviously. You probably want to set the MajorUnit of the value axis:

    With Me.chtGraph.Object.Application.Chart.Axes(xlValue)
    .MajorUnit = 100
    End With

    where chtGraph is the name of the chart control.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Setting columns in Graph to specific colours (

    Thank you, but io discovered a few hours back that the OnCurrent event was the place. I used it to get the MaxiumuScale and MajorUnit of both graphs on the form, then decided to make the smaller MaximumScale graph equal in size to the larger one.

    It works well, than you for your response. Actially that database that you pointed me to was a great help too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •