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.
![]() |
Patch reliability is unclear. Unless you have an immediate, pressing need to install a specific patch, don't do it. |
SIGN IN | Not a member? | REGISTER | PLUS MEMBERSHIP |
Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » 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.
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.
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
> 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.
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.
Donations from Plus members keep this site going. You can identify the people who support AskWoody by the Plus badge on their avatars.
AskWoody Plus members not only get access to all of the contents of this site -- including Susan Bradley's frequently updated Patch Watch listing -- they also receive weekly AskWoody Plus Newsletters (formerly Windows Secrets Newsletter) and AskWoody Plus Alerts, emails when there are important breaking developments.
Welcome to our unique respite from the madness.
It's easy to post questions about Windows 11, Windows 10, Win8.1, Win7, Surface, Office, or browse through our Forums. Post anonymously or register for greater privileges. Keep it civil, please: Decorous Lounge rules strictly enforced. Questions? Contact Customer Support.
Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.
Mastodon profile for DefConPatch
Mastodon profile for AskWoody
Home • About • FAQ • Posts & Privacy • Forums • My Account
Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts
Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.
Notifications