VBA to Conditionally Format a Chart (1 Viewer)

C

ChrisMore

Member
Local time
Today, 21:22
Joined
Jan 28, 2020
Messages
197
Hi,

Please can I have some help with VBA to dynamically change the color of a bar chart based on the value of "SumOfPercentage". It's frustrating that Access doesn't have a built in conditional formatting option to do this.

If value is under 50 - green
If value is between 50 and 90 - orange
If value is over 90 - red

I'm sure this is possible to achieve but haven't been able to find any help when researching this (if the answer is already available on a previous thread then I apologize). If there is an alternative method without needing code then I'd be happy to learn it.

Thanks for your help in advance.
Chris
1752666278404.png
 
I have seen where separate columns are added for each range so in your case there would be three extra columns with either the appropriate value or 0. Then create a stacked bar chart and set the colors as you desire.
 
DHookom said:
I have seen where separate columns are added for each range so in your case there would be three extra columns with either the appropriate value or 0. Then create a stacked bar chart and set the colors as you desire.
Click to expand...
Thanks for the suggestion. You say "set the colors as you desire" so do you mean coloring the chart bars manually? I require the chart colors to dynamically update as the value of "SumOfPercentage" changes for each category.
 
Gasman said:
Have a look here?
Click to expand...
I'm back to this project after a short break...
Thanks for the search suggestion, this has brought up some useful results.
I've looked at the one https://stackoverflow.com/questions/49456176/changing-the-color-of-a-bar-chart-in-access-using-vba
and the property "SeriesCollection" keeps coming up in order to change the chart colors.
However, just like the person who originally posted the above, I am unable to see this property when I put a break point after the chart object (graph3).
Code: 
Graph3.SeriesCollection(1).Points(1).Interior.Color = 39423
With this code I am getting the below error. Any ideas why I don't have theSeriesCollection property and seeing this error?

1754392511871.png
 
I know nothing about charts.
I would look at the Locals and walk that.
Chances are there is another level there?
 
AFAIAA, the ChartSeriesCollection only applies to Modern Charts in A365 and your screenshots indicate a classic chart is being used

With modern charts, I can use code such as this:

Code: 
 'set chart appearance
    With Me.Chart0.ChartSeriesCollection
       .Item(I).GridlinesType = Nz(Me.cboGridlinesType, 0)
       .Item(I).GridlinesColor = Nz(Me.txtGridlinesColor.BackColor, colGrey)
       .Item(I).FillColor = Nz(Me.txtFillColor.BackColor, colGrey)
       .Item(I).DisplayName = Nz(Me.txtDisplayName, "") 'used for legend
       .Item(I).TrendlineOptions = Nz(Me.cboTrendlineOptions, 0)
       .Item(I).TrendlineName = Nz(Me.txtTrendlineName, "") 'used for legend
    End With

If you have Access 365, you may want to try this with a modern chart. If so, see my article:
www.isladogs.co.uk

Programming Modern Chart Properties

This article demonstrates how to modify the properties of the various modern chart types using code. VBA support was added to the Beta Channel in version 2502 build 18514.20000 released on 17 Jan 2025. It will become available in other channels in the near future.
www.isladogs.co.uk www.isladogs.co.uk
 
This apparently works to some extent, so it should be there?

Post is from 2012, so should be Old Charts?

Code: 
Dim chtObj As Object
Dim j As Integer
Dim strType As String
Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long, c5 As Long

c1 = RGB(100, 100, 100)
c2 = RGB(100, 250, 250)
c3 = RGB(100, 200, 100)
c4 = RGB(200, 200, 100)
c5 = RGB(250, 100, 100)

Set chtObj = Me.gphHole.Object

For j = 1 To 5
    strType = chtObj.SeriesCollection(j).Points(1).DataLabel.Text
    Debug.Print strType
    chtObj.SeriesCollection(j).Points(1).Interior.Color = Switch(strType = "OL", c1, strType = "GP-GM(S)", c2, strType = "BLDRCBBL", c3, strType = "SPG", c4, strType = "TILL", c5)
Next

It also states 'NOTE: Detail section Format event only runs in PrintPreview or direct to printer.'

Original post
Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim chtObj As Object
Dim j As Integer
Dim strType As String
Dim c1 As Long, c2 As Long, c3 As Long, c4 As Long, c5 As Long
c1 = RGB(100, 100, 100)
c2 = RGB(100, 250, 250)
c3 = RGB(100, 200, 100)
c4 = RGB(200, 200, 100)
c5 = RGB(250, 100, 100)
Set chtObj = Me.gphHole.Object
For j = 1 To 5
    strType = chtObj.SeriesCollection(1).Points(j).DataLabel.Text
    chtObj.SeriesCollection(1).Points(j).Interior.Color = _
         Switch(strType = "OL", c1, strType = "GP-GM(S)", c2, strType = "BLDRCBBL", c3, strType = "SPG", c4, strType = "TILL", c5)
Next
End Sub
 
That code produces this.
However looking in Locals, I cannot see that SeriesCollection property, but the amended code runs fine?
1754394557236.png
 
You can create a stacked bar chart like this with no VBA:
1754395813212.png

I have a table of values with a column for each of the colors.
1754396129183.png


Create a query based on the following SQL:

SQL: 
SELECT tblForGraph.catName,
  IIf([catValue]<50,[CatValue],0) AS Green,
  IIf([catValue] Between 50 And 90,[CatValue],0) AS Orange,
  IIf([catValue]>90,[CatValue],0) AS Red
FROM tblForGraph;

Then create a horizontal stacked bar graph and manually set the colors.

Ideally, I would create a table of ranges with the appropriate color codes for the bars.
 
isladogs said:
AFAIAA, the ChartSeriesCollection only applies to Modern Charts in A365 and your screenshots indicate a classic chart is being used

With modern charts, I can use code such as this:

Code: 
 'set chart appearance
    With Me.Chart0.ChartSeriesCollection
       .Item(I).GridlinesType = Nz(Me.cboGridlinesType, 0)
       .Item(I).GridlinesColor = Nz(Me.txtGridlinesColor.BackColor, colGrey)
       .Item(I).FillColor = Nz(Me.txtFillColor.BackColor, colGrey)
       .Item(I).DisplayName = Nz(Me.txtDisplayName, "") 'used for legend
       .Item(I).TrendlineOptions = Nz(Me.cboTrendlineOptions, 0)
       .Item(I).TrendlineName = Nz(Me.txtTrendlineName, "") 'used for legend
    End With

If you have Access 365, you may want to try this with a modern chart. If so, see my article:
www.isladogs.co.uk

Programming Modern Chart Properties

This article demonstrates how to modify the properties of the various modern chart types using code. VBA support was added to the Beta Channel in version 2502 build 18514.20000 released on 17 Jan 2025. It will become available in other channels in the near future.
www.isladogs.co.uk www.isladogs.co.uk
Click to expand...
Thanks so much for the suggestion, I didn't know about the modern charts feature. I do have A365 so I've been playing around with a modern chart and looking at your impressive guide you linked. Is there a way to code the modern chart to automatically change the fill color of the chart bars based on the secondary axis values?
 
ChrisMore said:
Thanks so much for the suggestion, I didn't know about the modern charts feature. I do have A365 so I've been playing around with a modern chart and looking at your impressive guide you linked. Is there a way to code the modern chart to automatically change the fill color of the chart bars based on the secondary axis values?
Click to expand...
Not that I’m aware of but it’s not something that I’ve tried doing.
 
That’s what I thought 😏
Mind you Maria’s description may be hard to follow for anyone who hasn’t already seen Graph API in action
 
isladogs said:
Not that I’m aware of but it’s not something that I’ve tried doing.
Click to expand...
The more I play around with modern charts the more I'm finding them unintuitive. What would be simple to do with a classic chart is difficult or seemingly not possible with a modern chart.

I'm trying to remove the Y axis values, add grindlines to the X axis (horizontal) and add a custom format to the data labels. All easy to do with classic charts but can't work it out with these modern charts...

I'm not intending the thread to become a lesson in modern charts but if they are the way to go to recolor the chart bars with VBA then I at least need to match how my classic chart looked as a starting point.

I will say, modern charts are visually much more appealing than classic charts.
 
The two types of chart are managed in a totally different way.
For many years I preferred classic charts but modern charts have been significantly improved over the past year & I now use these instead.
There are many things that can be done in both chart types but each type has certain features that are unique to that

Suggest you read all 3 parts of the article I linked in post #7, download & play with the example app

By chance, I will be discussing modern charts at the start of my presentation to the Access Europe User Group later today starting at 18:00 UK time (UTC+1). For more details, see this post:

Access Europe User Group - Wed 6 Aug: New and Forthcoming Features in Access (Colin Riddington)

The next Access Europe User Group meeting will be on Wednesday 6August 2025 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). For local times, please check World Time Buddy I will be the presenter for this session and will be demonstrating several recently released new...
www.access-programmers.co.uk
You might find the session interesting / useful
 
Last edited:
isladogs said:
The two types of chart are managed in a totally different way.
For many years I preferred classic charts but modern charts have been significantly improved over the past year & I now use these instead.
There are many things that can be done in both chart types but each type has certain features that are unique to that

Suggest you read all 3 parts of the article I linked in post #7, download & play with the example app

By chance, I will be discussing modern charts at the start of my presentation to the Access Europe User Group later today starting at 18:00 UK time (UTC+1). For more details, see this post:

Access Europe User Group - Wed 6 Aug: New and Forthcoming Features in Access (Colin Riddington)

The next Access Europe User Group meeting will be on Wednesday 6August 2025 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). For local times, please check World Time Buddy I will be the presenter for this session and will be demonstrating several recently released new...
www.access-programmers.co.uk
You might find the session interesting / useful
Click to expand...
Thanks, that does sound interesting. I'll try and join your meeting later.
 
You must log in or register to reply here.

Similar threads

S
Format number with condition
Replies
3
Views
276
CJ_London
C
J
Conditional Format
Replies
2
Views
489
arnelgp
arnelgp
klsblues
VBA Conditional Formatting error because I have more than 3 formatting requirements.
2 3
Replies
53
Views
2,504
AHeyne
A
A
Solved Conditional hyperlink formatting
Replies
4
Views
460
AlliCarr
A
J
undo conditional format.
Replies
8
Views
559
John Sh
J

Users who are viewing this thread

  • Total: 2 (members: 1, guests: 1)
    Back
    Top Bottom