VBA to Conditionally Format a Chart

[ChrisMore]

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

 

[DHookom]

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.

 

[ChrisMore]

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.

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]

Have a look here?

 

[ChrisMore]

Have a look here?

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

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?

 

[Gasman]

I know nothing about charts.
I would look at the Locals and walk that.
Chances are there is another level there?

 

[isladogs]

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:

 '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:

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

 

[Gasman]

This apparently works to some extent, so it should be there?

Post is from 2012, so should be Old Charts?

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

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

 

[Gasman]

That code produces this.
However looking in Locals, I cannot see that SeriesCollection property, but the amended code runs fine?

 

[DHookom]

You can create a stacked bar chart like this with no VBA:

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

Create a query based on the following 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.

 

[ChrisMore]

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:

 '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:

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

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?

 

[DHookom]

You might be interested in this Zoom meeting of the Access Lunchtime user group.

Update on calling Microsoft Graph API from VBA with Maria Barnes​

 

[isladogs]

You might be interested in this Zoom meeting of the Access Lunchtime user group.

Update on calling Microsoft Graph API from VBA with Maria Barnes​

Why? The misleadingly named Graph API has nothing to do with charting.
The AL session is about emailing from Access but using that API instead of Outlook

 

[isladogs]

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?

Not that I’m aware of but it’s not something that I’ve tried doing.

 

[DHookom]

Why? The misleadingly named Graph API has nothing to do with charting.
The AL session is about emailing from Access but using that API instead of Outlook

Sorry, I had simply read the misleading title.

 

[isladogs]

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