• Setting columns in Graph to specific colours (Access 2003)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Setting columns in Graph to specific colours (Access 2003)

    Author
    Topic
    #445584

    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.

    Viewing 0 reply threads
    Author
    Replies
    • #1080773

      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.

      • #1080779

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

        • #1080782

          See previous reply – I’d need to know details.

          • #1080785

            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.

            • #1080805

              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.

            • #1080889

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

            • #1080952

              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.ColorIndex = 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

            • #1080954

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

            • #1081701

              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?

            • #1081712

              > 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.

            • #1081714

              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.

    Viewing 0 reply threads
    Reply To: Setting columns in Graph to specific colours (Access 2003)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: