Chart Object and VBA.
Working with the Microsoft Access Chart Object in VBA can be quite complex — but it’s also a fascinating and rewarding process. Once the code is properly developed, it can be reused across different Access databases to create charts with a consistent look and feel in just a few minutes.
There are numerous chart types, chart areas, and object groups available, each with a wide range of configurable properties. Some of these properties, such as Auto Scaling, are specific to certain chart types — for example, 3D charts.
Note: Before proceeding, ensure that the following essential reference libraries are attached to your database. This will prevent VBA runtime errors when working with chart-related code.
Attaching System Reference Libraries.
-
Open the Visual Basic Editor by selecting Tools → Visual Basic Editor or choosing Code from the View menu.
-
From the Tools menu, select References.
-
In the Available References dialog box, check the following libraries:
-
Microsoft Office 12.0 Object Library
-
Microsoft Excel 12.0 Object Library
-
These libraries are also required to run the demo database provided for download at the end of this page.
After some research and experimentation, I developed a VBA routine that can dynamically modify and format Microsoft Access charts with a clean, professional appearance. I hope you’ll enjoy working with it as much as I did. A sample result is shown in the image below.
Formatting Chart Elements.
The resizing of the chart and formatting of its Report various elements — including the Chart Title, Axis Titles, Chart Area, Plot Area, Chart Bars with Gradient Colors, Legends, Grid Lines, Data Labels, and the alignment of Data Labels — are all controlled through VBA code. Placing a chart object on a report with minimal property settings is done manually as a starting point.
When the program is run, it presents three options for the chart type:
-
Bar Chart
-
Line Chart
-
Pie Chart
The same chart object is dynamically transformed into the selected type, using sample values from a table for demonstration purposes. The Pie Chart uses the first record from the table, and its data handling differs from the Bar and Line Charts. In this example, the Pie Chart is included for demonstration only and is not treated as a separate, fully detailed case.
Color Numbers
The following Color index numbers can be used in different combinations in the Program to get different gradient shades.
Preparing for Trial Run.
Follow the simple steps explained below, and get prepared for the trial run.
Create a Table with the following Structure and save it with the name Table1.
Add the sample records with the values shown above.
- Open a new Report in Design View.
- Select Object from the Insert Menu and select Microsoft Graph 2000 Chart (or Microsoft Graph Chart in MS-Access 2003) from the Object Type List.
The inserted chart object with default values will be in an activated state, displaying the default values in the Data Grid.
Click outside the chart in the Detail Section of the Report to deactivate and deselect the Chart.
Click on the Chart again to select it and display the Property Sheet (View ->Properties)
Change the following Property Values as given below:
Name: Chart1
Row Source Type: Table/Query
Row Source: Table1
Column Heads: Yes
Even though the following properties can be changed manually, these are changed through the Code:
- SizeMode : 3 (Zoom)
Column Count: Number of Columns in the Source Data
Left: 0.2917?
Top: 0.2708?
Width: 5.5729?
Height: 4.3854?
2D Chart Area Zones.
An MS Access 2D Chart Object has 3 different areas:
- the outer Frame
Chart Area where Chart Title, Axis Titles, and Legend Values appear.
Plot Area where the chart Data Series Values, Data Labels, Gridlines, Scale, and Category Values appear in the Bar or Line Charts.
If the Size Mode property is set to Clip (value 0), resizing the chart using the last four properties (Left, Top, Width, Height) will only adjust the outer frame, leaving the inner sections — the Chart Area and Plot Area — unchanged. If the property is set to Stretch or Zoom (values 1 or 3, respectively), the inner chart sections will resize to fit within the outer frame. Zoom is the preferred setting, as it maintains the correct aspect ratio of the chart during resizing, preventing distortion.
The Column Count property is determined from the Row Source table or query.
The program also validates the Row Source Type property. If it is not set to Table/Query, it will be corrected. However, if the Row Source property does not contain a valid table name or SQL string, the program will terminate and display an error message.
- Save the Report with the Name myReport1.
- Copy and Paste the following VBA Code into the Global Module and save the Module.
Public Function ChartObject(ByVal ReportName As String, ByVal ChartObjectName As String) '------------------------------------------------ 'Author : a.p.r. pillai 'Date : June-2008/Revision on 12/2019 'Source Code : from Microsoft Access Help 'and modified certain parameters '------------------------------------------------ Dim Rpt As Report, grphChart As Object Dim msg As String, lngType As Long, cr As String Dim ctype As String, typ As Integer, j As Integer Dim db As Database, rst As Recordset, recSource As String Dim colmCount As Integer Const twips As Long = 1440 'On Error GoTo ChartObject_Err cr = vbCr & vbCr msg = "1. Bar Chart" & cr msg = msg & "2. Line Chart" & cr msg = msg & "3. Pie Chart" & cr msg = msg & "4. Quit" & cr msg = msg & "Select Type 1,2 or 3" ctype = "": typ = 0 Do While typ < 1 Or typ > 4 ctype = InputBox(msg, "Select Chart Type") If Len(ctype) = 0 Then typ = 0 Else typ = Val(ctype) End If Loop Select Case typ Case 4 Exit Function Case 1 lngType = xlColumnClustered Case 2 lngType = xlLine Case 3 lngType = xl3DPie End Select DoCmd.OpenReport ReportName, acViewDesign Set Rpt = Reports(ReportName) Set grphChart = Rpt(ChartObjectName) grphChart.RowSourceType = "Table/Query" recSource = grphChart.RowSource If Len(recSource) = 0 Then MsgBox "RowSource value not set." Exit Function End If 'get number of columns in chart table/Query Set db = CurrentDb Set rst = db.OpenRecordset(recSource) colmCount = rst.Fields.Count rst.Close With grphChart .ColumnCount = colmCount .SizeMode = 3 .Left = 0.2917 * twips .Top = 0.2708 * twips .Width = 5.5729 * twips .Height = 4.3854 * twips End With grphChart.Activate 'Chart type, Title, Legend, Datalabels,Data Table With grphChart .chartType = lngType ' .chartType = xlColumnClustered '.AutoScaling = True ' only for 3D type .HasLegend = True .HasTitle = True .ChartTitle.Font.Name = "Verdana" .ChartTitle.Font.Size = 14 .ChartTitle.Text = "Revenue Performance - Year 2007" .HasDataTable = False .ApplyDataLabels xlDataLabelsShowValue End With 'apply gradient color to Chart Series If typ = 1 Or typ = 2 Then For j = 1 To grphChart.SeriesCollection.Count With grphChart.SeriesCollection(j) '.Interior.Color = RGB(Int(Rnd(j) * 200), Int(Rnd(j) * 150), Int(Rnd(j) * 175)) .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608 .Fill.Visible = True .Fill.ForeColor.SchemeColor = Int(Rnd(1) * 54) + 2 If typ = 1 Then .Interior.Color = msoGradientVertical End If .DataLabels.Font.Size = 10 .DataLabels.Font.Color = 3 If typ = 1 Then .DataLabels.Orientation = xlUpward Else .DataLabels.Orientation = xlHorizontal End If End With Next j End If If ctype = 3 Then GoTo nextstep 'skip axes for pie chart End If 'Y-Axis Title With grphChart.Axes(xlValue) .HasTitle = True .HasMajorGridlines = True With .AxisTitle .Caption = "Values in '000s" .Font.Name = "Verdana" .Font.Size = 12 .Orientation = xlUpward End With End With 'X-Axis Title With grphChart.Axes(xlCategory) .HasTitle = True .HasMajorGridlines = True .MajorGridlines.Border.Color = RGB(0, 0, 255) .MajorGridlines.Border.LineStyle = xlDash With .AxisTitle .Caption = "Quarterly" .Font.Name = "Verdana" .Font.Size = 10 .Font.Bold = True .Orientation = xlHorizontal End With End With With grphChart.Axes(xlValue, xlPrimary) .TickLabels.Font.Size = 10 End With With grphChart.Axes(xlCategory) .TickLabels.Font.Size = 10 End With nextstep: With grphChart .ChartArea.Border.LineStyle = xlDash .PlotArea.Border.LineStyle = xlDot .Legend.Font.Size = 10 End With 'Chart Area Fill with Gradient Color With grphChart.ChartArea.Fill .Visible = True .ForeColor.SchemeColor = 2 .BackColor.SchemeColor = 19 .TwoColorGradient msoGradientHorizontal, 2 End With 'Plot Area fill with Gradient Color With grphChart.PlotArea.Fill .Visible = True .ForeColor.SchemeColor = 2 .BackColor.SchemeColor = 10 .TwoColorGradient msoGradientHorizontal, 1 End With grphChart.Deselect DoCmd.Close acReport, ReportName, acSaveYes DoCmd.OpenReport ReportName, acViewPreview ChartObject_Exit: Exit Function ChartObject_Err: MsgBox Err.Description, , "ChartObject()" Resume ChartObject_Exit End FunctionRunning the Code.
Open the VBA Module that you have pasted the code into if you have closed it.
Run the code from the Debug Window (press Ctrl+G to display the Debug Window) using the following syntax:
ChartObject "myReport1", "Chart1"
Or call the function from the On_Click() Event Procedure of a Command Button on a Form.
Select Chart Type 1, 2, or 3 for Bar Chart, Line Chart, or Pie, respectively.
The VBA Code.
The Program will open the Report myReport1 in Design View, modify the Graph Chart for the selected Type, save it, and then reopen it in Print Preview. You may minimize the VBA Window (Alt+F11) to view the Chart. Since the Report is saved after the changes, you may open it manually in Design View or Print Preview.
When you run the Bar Chart or Line Chart Code, the Gradient Scheme Color Codes are selected randomly, which will apply different shades of Gradient Colors every time.
NB: Any suggestions for improvement are welcome.
Download the Demo Database.
Download Demo Database 
Download Repairing.zip
