Page 1 Page 2 Page 3

Modern Charts Improvements: Part 3 - Use VBA Code to set Chart Properties

First Published 22 Jan 2025 ARTICLE IN PROGRESS Last Updated 31 Jan 2025 Difficulty level: Easy

I am grateful to fellow Access developer Mark Curry for alerting me to the fact that the long awaited VBA support for modern charts in Access is now available in the Beta Channel.
It was included in the Beta Channel update to version 2502 build 18514.20000 released on 17 Jan 2025 and it is planned to be rolled out to the current channel in early March.

Many thanks to Access team member, Courtney Owen, who has led the development of the improvements to modern charts over the past year or so.

The new functionality means you can now allow users to modify the appearance of modern charts at runtime. For example, features such as gridline type / colors and sort order.

It has always been possible to set certain modern chart options in code. Those are the items available from the property sheet including:
• background color / border style
• chart title items: text / font name / font color / font size
• primary / secondary & category axes items: text / font name / font color / font size
• legend items: position / font color / font size

Until now, all other modern chart properties & settings could only be modified from a combination of the the property sheet and chart settings task pane.
These were only available in design view.

The new VBA support means that a much wider range of chart and series properties are also now available in code including:
• chart type
• gridline type / gridline color
• sort order
• line weight / marker points etc (line chart)
• DataLabelDisplayFormat / DataLabelDisplayPosition
• DashType
• DisplayBoxWhiskerDataPoints / DisplayBoxWhiskerMeanMarker
• ParetoLineColor
• WordCloudShape / WordCloudOrientation

This article will explain how each property can be modified in code. An example app will be provided with all the code used in the article.

There are currently 16 different chart types, several of which have sub-types giving 25 different chart types in total.
The chart type property is acChartType with enums from 0 to 24 and property values such as acChartColumnClustered / acChartPareto etc.

A simplified property name such as Column Clustered and Pareto is shown on the property sheet.

Chart Type Enums
Either the enum or property value can be used in code.

The video below (04:57) shows how the different chart types can be set at runtime e.g. from a combobox or by looping through each chart type in turn from a command button:

In the form used for the above video, the combo box has two columns:
• Column(0) - Enum (bound)
• Column(1) - PropertyName

The code to change the chart type and the chart subtitle is very simple:

Private Sub cboChartType_AfterUpdate()

Me.Chart0.ChartType = Me.cboChartType
Me.Chart0.ChartSubtitle = Me.cboChartType.Column(1) & " Chart"

End Sub

The chart series collection contains all 27 of the different chart series members. The property values and names are also shown for two of those series members (DashType and GridlineType):

The next video (06:47) demonstrates how the chart sort order together with certain series options such as fill color / show data label / gridlines type & color can be sert in code at runtime:

The code to change the chart sort order and the chart series options (display name, fill color, show data label, gridline type/color) is:

Dim I As Integer

Private Sub cboSortOrder_AfterUpdate()

'applies to all series
With Me.Chart0
For Each chtSeries In .ChartSeriesCollection
'0;none;1;ascending;2;descending
chtSeries.SortOrderType = Me.cboSortOrder
Next
End With

End Sub
'-----------------------------------------

Private Sub chkDisplayDataLabel_Click()

'loop through and apply to each individual series
With Me.Chart0
I = 0
For Each chtSeries In .ChartSeriesCollection
If I = Me.txtSeriesItem Then chtSeries.DisplayDataLabel = Me.chkDisplayDataLabel
I = I + 1
Next
End With

End Sub
'-----------------------------------------

Private Sub cmdGridlineColor_Click()

'Show color dialog for series gridline color

'Normally, the gridline option for the last series is applied
'However, this can be circumvented by setting one series to minor and another to major

Dim oleNewColor As OLE_COLOR
oleNewColor = ShowColorDialog(Me.Hwnd, True, colGrey)
If oleNewColor <> -1 Then
Me.txtGridlineColor.BackColor = oleNewColor
Me.txtGridlineColor.ForeColor = Me.txtGridlineColor.BackColor
End If
With Me.Chart0
I = 0
For Each chtSeries In .ChartSeriesCollection
'Need to use Long values for color
If I = Me.txtSeriesItem Then chtSeries.GridlinesColor = oleNewColor
I = I + 1
Next
End With

End Sub
'-----------------------------------------

Private Sub cmdFillColor_Click()

'Show color dialog for series fill color

Dim oleNewColor As OLE_COLOR
oleNewColor = ShowColorDialog(Me.Hwnd, True, colGrey)
If oleNewColor <> -1 Then
Me.txtFillColor.BackColor = oleNewColor
Me.txtFillColor.ForeColor = Me.txtFillColor.BackColor
End If

With Me.Chart0
I = 0
For Each chtSeries In .ChartSeriesCollection
'Need to use Long values for color
If I = Me.txtSeriesItem Then chtSeries.FillColor = oleNewColor
I = I + 1
Next
End With

End Sub
'-----------------------------------------

Private Sub txtDisplayName_AfterUpdate()

With Me.Chart0
I = 0
For Each chtSeries In .ChartSeriesCollection
'legend is based on display name
If I = Me.txtSeriesItem Then chtSeries.DisplayName = Me.txtDisplayName
I = I + 1
Next
End With

End Sub

NOTE: The ShowColorDialog function used above is placed in a standard module. The full module code is available to download in a separate article: Show Color Dialog

MORE EXAMPLES AND CODE COMING SOON!

Feedback

Please use the contact form below to let me know whether you found this article interesting/useful or if you have any questions/comments.

Please also consider making a donation towards the costs of maintaining this website. Thank you

Colin Riddington Mendip Data Systems Last Updated 31 Jan 2025

Return to Access Articles Page Page 3 of 3 1 2 3 Return To Top