PIE Chart Object and VBA

PIE Chart Object and VBA.

This post continues our discussion on working with the Chart Object in VBA. In the earlier article, Working with Chart Object in VBA, we created a small table and a report containing a Chart Object, and we explored a few basic property modifications to prepare for the upcoming VBA demonstrations.

Before proceeding, please review Steps 1 to 7 outlined in the earlier post by following the provided link, and then continue from here.

You will find it helpful to read the related topics, Column Chart and VBA, for additional context and practical examples.

PIE Chart Options.

If you have already tried the code examples from the earlier posts, you should find most of the segments here familiar and easy to follow. The main differences in this section involve the formatting of individual pie slices and a few property adjustments.

In this example, we will experiment with three types of Pie Charts:

1. 3D Pie

2. 3D Exploded Pie

3. Pie of Pie

Each variation demonstrates a different way of projecting data segments and controlling the visual layout of your chart through VBA.

1. Copy and paste the code given below into a Global Module in your Database.

   Public Function PieChart(ByVal ReportName As String, ByVal ChartObjectName As String)
   '---------------------------------------------------
   'Author : a.p.r. pillai
   'URL    : http://www.msaccesstips.com
   'Date   : July-2008
   'Customized Source Code : from Microsoft Access Help
   '---------------------------------------------------
   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, chartType(1 To 5) As String
   Const twips As Long = 1440
   
   On Error GoTo PieChartChart_Err
   
   chartType(1) = "3D Pie Chart"
   chartType(2) = "3D Pie Exploded"
   chartType(3) = "Pie of Pie"
   chartType(4) = "Quit"
   chartType(5) = "Select 1-4, 5 to Cancel"
   
   cr = vbCr & vbCr
   msg = ""
   For j = 1 To 5
     msg = msg & j & ". " & chartType(j) & cr
   Next
   
   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 = xl3DPie
       Case 2
          lngType = xl3DPieExploded
       Case 3
          lngType = xlPieOfPie
   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, aborted."
      Exit Function
   End If
   
   'get number of columns in chart table/Query
   'if Table or SQL string is not valid then
   'generate error and exit program
   Set db = CurrentDb
   Set rst = db.OpenRecordset(recSource)
   colmCount = rst.Fields.Count
   rst.Close
   
   're-size the Chart
   With grphChart
       .ColumnCount = colmCount
       .SizeMode = 3
       .Left = 0.2917 * twips
       .Top = 0.2708 * twips
       .Width = 5 * twips
       .Height = 4 * twips
   End With
   
   'activate the chart for modification.
   grphChart.Activate
   
   'Chart type, Title, Legend, Datalabels,Data Table
   With grphChart
        .chartType = lngType
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Font.Name = "Verdana"
       .ChartTitle.Font.Size = 14
       .ChartTitle.Text = chartType(typ) & " Chart."
       .HasDataTable = False
   End With
   
   'format Pie slices with gradient color
   With grphChart.SeriesCollection(1)
       .HasDataLabels = True
       .DataLabels.Position = xlLabelPositionBestFit
       .HasLeaderLines = True
       .Border.ColorIndex = 19 'edges of pie shows in white color
       For j = 1 To .Points.Count
           With .Points(j)
               .Fill.ForeColor.SchemeColor = Int(Rnd(Timer()) * 54) + 2
               .Fill.OneColorGradient msoGradientVertical, 4, 0.231372549019608
               .DataLabel.Font.Name = "Arial"
               .DataLabel.Font.Size = 10
               .DataLabel.ShowLegendKey = False
               '.ApplyDataLabels xlDataLabelsShowValue
               .ApplyDataLabels xlDataLabelsShowLabelAndPercent
           End With
       Next
   End With
   
   'Chart Area Border
   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 = 17
       .BackColor.SchemeColor = 2
       .TwoColorGradient msoGradientHorizontal, 2
   End With
   
   'Plot Area fill with Gradient Color
   With grphChart.PlotArea.Fill
       .Visible = True
       .ForeColor.SchemeColor = 6
       .BackColor.SchemeColor = 19
       .TwoColorGradient msoGradientHorizontal, 1
   End With
   
   grphChart.Deselect
   
   DoCmd.Close acReport, ReportName, acSaveYes
   DoCmd.OpenReport ReportName, acViewPreview
   
   PieChart_Exit:
   Exit Function
   
   PieChart_Err:
   MsgBox Err.Description, , "PieChart()"
   Resume PieChart_Exit
   End Function
   

2. Press Ctrl+G to display the Debug Window (Immediate Window) in the VBA Editing View, type the following command in the Debug Window, and press Enter:

PieChart "MyReport1", "Chart1"

Sample run: an image of Option-1:

Right-click to open Large Image in New Tab/Window.

Sample run: an image of Option-2:

Right-click to open Large Image in New Tab/Window.

Sample run: an image of Option-3:

Right-click to open Large Image in New Tab/Window.

Run it from the Command Button Click.

You can run this program from the On_Click() Event Procedure of a Command Button on your Form. The program will:

1. Open myReport1 in Design View.

2. Resize the Chart1 Chart Object to a size suitable for printing or viewing.

3. Format the chart elements.

4. Save the report and reopen it in Print Preview.

Key points about PIE Charts:

• The analysis of values in a Pie Chart differs from other chart types, which is why Pie Charts accept only one set of values.

• For example, if four people decide to buy a Cake worth $100, and their shares are $60, $20, $14, and $6, the Pie Slices will reflect the proportion of each share relative to the Total Cost.

• Unlike Bar or Column Charts, where formatting can be applied to an entire data series using grphChart.SeriesCollection() - Pie Charts require you to access individual slices through grphChart.SeriesCollection().Points() - This allows you to apply different gradient colors or formatting to each slice separately.

Tip: You can use the same Points() method to format individual bars of a Bar Chart, but you must remove the Pie-specific properties from the code to avoid errors.

.DataLabels.Position = xlLabelPositionBestFit
.HasLeaderLines = True
.Border.ColorIndex = 19 'showing edges of Pie slices with white color
.ApplyDataLabels xlDataLabelsShowLabelAndPercent

Chart Formatting.

In Pie Charts, the X-Axis and Y-Axis titles are not used, so the corresponding code segments have been removed from the program. The Chart Area and Plot Area are formatted using a different set of light colors, distinct from the previous examples.

You can also use the Rnd() function to generate different color sets for each run, applying gradient formatting. However, the resulting color combination may not always be ideal for viewing or printing. If you are not satisfied with the current color scheme, you can experiment with different color values from the Color Chart provided in the first post, Working with Chart Object in VBA.

Download the Demo Database.

Download Demo Database

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

Column Chart and VBA

Column Chart VBA.

In our earlier example, working with Access Graph Chart in VBA, we prepared MS Access Charts with VBA and ran sample tests for three types of charts: Clustered Column, Line, and Pie. If this is your first time here, refer to the earlier post by clicking the link above. Follow Steps 2 to 7 to complete the preparations before trying the code presented here.

Previously, we were able to change the chart type in code to any of the three types while applying common property settings. However, some properties, such as Chart Axis Titles, were skipped conditionally for the Pie Chart. We will handle Pie Charts separately later, including formatting individual pie slices and modifying other settings.

Here, we will focus only on Column Charts and run the code for four different designs. Column Charts, with vertical bars, are the most commonly used type and are often loosely referred to as bar charts. The category labels for the plotted values appear horizontally along the X-axis below the chart, while the chart scale is calculated automatically and displayed vertically along the left side (Primary Y-Axis). This scale can also be set manually if needed.

When small values are plotted alongside large values, the differences in the small values may not be clearly visible. In such cases, you can plot the small value series on the Secondary Y-Axis (right-side vertical axis) to make the variations more noticeable. Adding data labels further enhances the readability and analysis of the chart.

For 3D Column Charts, the Y-axis is replaced by a Z-axis to display tick labels and axis titles. Note that horizontal bar charts are the true representation of bar charts.

Sample Demo Run Code

We have created options in the Code to run for the following Options:

1. Column Clustered.
2. Column Clustered (Reverse Plot Order - flipped upside down)
3. 3D Column Clustered.
4. 3D Column Stacked.

There are optional properties in this Code that you can use to change the shape of the Bars to a Cone or a Cylinder.

In this example, we enable the HasDataTable property of the Chart Object, which displays the source data table directly on the chart. The X-Axis category labels, Qrtr1 to Qrtr4, become part of this data table display. Note that if you try to modify the X-Axis Tick-Label properties, such as Font or Font Size, while the data table is visible, you may encounter errors. We have included this code conditionally rather than removing it completely, so you can observe the difference.

The DataLabels Orientation property allows you to tilt the data labels to a specified angle in degrees, in addition to the standard horizontal or vertical display.

To try out these examples, follow the steps below:

The VBA Code.

1. Copy and paste the following code into the Global Module and save it. If you have already gone through the earlier Post and gone through steps 2 to 7, then you are ready to run the Code.

   Public Function ColumnChart(ByVal ReportName As String, ByVal ChartObjectName As String)
   '---------------------------------------------------
   'Author : a.p.r. pillai
   'Date   : June-2008
   'URL    : http://www.msaccesstips.com
   'Customized Source Code : from Microsoft Access Help
   '---------------------------------------------------
   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, chartType(1 To 6) As String
   Const twips As Long = 1440
   
   On Error GoTo ColumnChart_Err
   
   chartType(1) = "Clustered Column"
   chartType(2) = "Reverse Plot Order"
   chartType(3) = "3D Clustered Column"
   chartType(4) = "3D Stacked Column"
   chartType(5) = "Quit"
   chartType(6) = "Select 1-4, 5 to Cancel"
   
   cr = vbCr & vbCr
    msg = ""
   For j = 1 To 6
     msg = msg & j & ". " & chartType(j) & cr
   Next
   
   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 5
           Exit Function
       Case 1,2
          lngType = xlColumnClustered
       Case 3
          lngType = xl3DColumnClustered
       Case 4
          lngType = xl3DColumnStacked
   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, aborted."
      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 * twips
       .Height = 4 * twips
   End With
   
   grphChart.Activate
   
   'Chart type, Title, Legend, Datalabels,Data Table
   With grphChart
        .chartType = lngType
        If typ = 3 Or typ = 4 Then 
   ' for 3D Charts only
          .RightAngleAxes = True
          .AutoScaling = True
        End If
       .HasLegend = True
       .HasTitle = True
       .ChartTitle.Font.Name = "Verdana"
       .ChartTitle.Font.Size = 14
       .ChartTitle.Text = chartType(typ) & " Chart."
       .HasDataTable = True
       .ApplyDataLabels xlDataLabelsShowValue
   End With
   
   'apply gradient color to Chart Series    
   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(Timer()) * 54) + 2
           '.Barshape = xlCylinder 
   ' xlCylinder, xlConeToPoint, xlBox, xlPiramidtoMax
           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
               .DataLabels.Orientation = 45 'titlted angle in degrees
           End If
           End With
       Next
   
   'Y-Axis /(Z-Axis for 3D)Title
   With grphChart.Axes(xlValue)
      If typ = 2 Then
       .ReversePlotOrder = True
    'flips upside down
      Else
       .ReversePlotOrder = False
      End If
       .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
   
   'Primary Y/Z Axis values label's font size
   With grphChart.Axes(xlValue, xlPrimary)
        .TickLabels.Font.Size = 10
   End With
   
   'X-Axis category Labels (Qrtr1, Qrtr2...)
   If grphChart.HasDataTable = False Then
       With grphChart.Axes(xlCategory)
           .TickLabels.Font.Size = 8
       End With
   Else
       grphChart.DataTable.Font.Size = 9
   End If
   
   'Chart Area Border
   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 = 42
       .TwoColorGradient msoGradientHorizontal, 1
   End With
   
   grphChart.Deselect
   
   DoCmd.Close acReport, ReportName, acSaveYes
   DoCmd.OpenReport ReportName, acViewPreview
   
   ColumnChart_Exit:
   Exit Function
   
   ColumnChart_Err:
   MsgBox Err.Description, , "ColumnChart()"
   Resume ColumnChart_Exit
   End Function
   

   Preparing for Demo Run.

2. Insert a new MS-Office Graph Chart Object on a new Report, change the basic property values as given in the earlier Article (Working with Graph Chart in VBA), and save the Report with the name myReport2. We can use the same Table that we have created for earlier examples.

   NB: Always create a new Chart Object for a new set of examples rather than using the same Chart created earlier. Some property changes were found to be shown incorrectly when reused for different Chart Types.

3. The Code can be tested either by running from a Command Button Click Event Procedure or directly from the Debug Window (Immediate Window). Press Alt+F11 to open the VBA Window,  press Ctrl+G for the Debug Window, type the following command, and press the Enter Key.

   ColumnChart "myReport2", "Chart1"

4. A menu will appear with four Column Chart Options. Type 1 in the Text Box and press the Enter key to run the first option.

   The Program Opens the Report myReport2 in Design View, modifies the Chart Properties, saves the Report with the Chart, and then re-opens it in Print Preview. A sample run image is given below.



Right-click to open Large Image in New Tab/Window.

5. Run the program a second time, and this time select Option number 2.

In this run, the ReversePlotOrder property is set to True to flip the Chart upside down. The sample image is given below.

Right-click to open Large Image in New Tab/Window.

Every time you run the Code, the Colors are selected in Random order from the Color Chart given in the earlier Post (Working with Graph Chart in VBA) and will display the Chart Bars differently.

Saving the Report with the Chart.

If you plan to save the Chart and don't like the Color combination currently displayed, run the option more than once till you get the color combination to your liking. If you want to save the current run of the Chart, make a copy of the Report or Export the Report into MS-Access Snapshot Viewer Format (MS-Access 2000 or later).

Chart Area and Plot Area are assigned a light Gradient Color, better when printed.

Sample Run of Option 3.

Right-click to open Large Image in New Tab/Window.

Sample Run of Option 4.

Right-click to open Large Image in New Tab/Window.

In Options 3 and 4 Runs, you can see that the Plot Area of the Chart is extended to display the Data Table and X-Axis Title. But, in the first two runs, these are appearing outside the Plot Area.

Saving PowerPoint Presentations.

You can copy and paste the finished Chart into PowerPoint Presentations. Before transporting the Chart to the PowerPoint page, copy and paste the Values from the Table into the Chart's Data Grid so that you can edit the values in PowerPoint itself if needed.

1. Double-click on the Chart Object in Report Design View to activate the Chart and to display the Data Grid.
2. Open the Table in Datasheet View, and click on the top left corner of the Table to highlight all the records.
3. Select Copy from the Edit Menu.
4. Right-click on the top-left corner cell of the Data Grid and select Paste from the displayed Shortcut Menu.

Download the Demo Database.

Download Demo Database

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

Working with Chart Object in VBA

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.

1. Open the Visual Basic Editor by selecting Tools → Visual Basic Editor or choosing Code from the View menu.

2. From the Tools menu, select References.

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

Right-click to open Large Image in New Tab/Window.

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.

Right-click to open Large Image in New Tab/Window.

Preparing for Trial Run.

Follow the simple steps explained below, and get prepared for the trial run.

1. Create a Table with the following Structure and save it with the name Table1.

   

   Right-click to open Large Image in New Tab/Window.

2. Add the sample records with the values shown above.

3. Open a new Report in Design View.
4. 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.

   

   Right-click to open Large Image in New Tab/Window.

5. Click outside the chart in the Detail Section of the Report to deactivate and deselect the Chart.

6. Click on the Chart again to select it and display the Property Sheet (View ->Properties)

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

   1. the outer Frame

   2. Chart Area where Chart Title, Axis Titles, and Legend Values appear.

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

8. Save the Report with the Name myReport1.

The VBA Code.

9. 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 Function
   

   Running the Code.

10. Open the VBA Module that you have pasted the code into if you have closed it.

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

12. Select Chart Type 1, 2, or 3 for Bar Chart, Line Chart, or Pie, respectively.

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

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Object in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line-Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

MS-Access and Graph Charts2

Continuation of Last Week's Discussion

If you have landed straight on this page, please go through the earlier Post MS-Access & Graph Charts and then continue.

Sample Data for Pie Chart

Desc	Veh Sales	Parts Sales	Service Sales
Total Sales	450000	645000	25000

1. Create a Table with the above structure and sample data, and save the Table as pie_Table.
2. Open a new Report in the design view. Select the Object option from the Insert Menu, select Microsoft Graph-Chart, and then click OK. A Chart Object with default values is inserted into the Report.
3. Click outside the chart to deselect and disable the Edit Mode. Click again on the chart to select it, display the property sheet, and change the following values:
   • Size Mode = Zoom
   • Row Source Type = Table/Query
   • Row Source = Pie_Table
   • Column Heads = Yes
   • Left = 0.3"
   • Top = 0.3"
   • Width = 6.0"
   • Height = 4.0"

4. Creating and Formatting a 3-D Pie Chart.

   1. Double-click on the chart control in your report or form to open the Chart Formatting Toolbar.

   2. From the Chart Type Toolbar, select 3-D Pie Chart.
      Alternatively:

      • Right-click on a blank area inside the chart (not on the pie itself).

      • Choose Chart Type from the shortcut menu.

      • Select 3-D Pie Chart and click OK.

   3. Open the Pie_Table:

      • Click on the top-left corner of the datasheet (grid) to select all data.

      • Select Edit > Copy from the menu.

   4. Go back to your chart:

      • Click the top-left corner of the chart's datasheet grid.

      • Select Edit > Paste to paste the data.

   5. Remove extra sample data:

      • If any extra rows or columns remain after pasting, select them and use Edit > Cut to delete.

   6. Resize the Pie:

      • Click on the shaded area surrounding the pie to select it.

      • Drag the bottom-right sizing handle outward to enlarge the pie slightly.

   7. Format the Plot Area:

      • Right-click on the shaded area around the pie.

      • Select Format Plot Area from the shortcut menu.

      • Set Area Options to None.

      • Set Border Options to None, then click OK.

   8. Set Chart Title and Data Labels:

      • Right-click on an empty area of the chart.

      • Select Chart Options.

      • On the Titles tab, enter "Total Revenue" in the Chart Title box.

      • Switch to the Data Labels tab, check the Percentage option under "Label Contains".

      • Click OK to apply your changes.

Click to Enlarge

A chart with more than one set of Bars.

Table1

Desc	Qtr1	Qtr2	Qtr3	Qtr4
A_Revenue	25000	35000	20000	40000
B_Expenses	15000	20000	13000	17000
C_Income	10000	15000	7000	23000

Create a table using the field structure and data provided above, and save it as Table1. Then, follow the same steps outlined in the earlier post, MS-Access and Graph Charts, starting from Step 4, to create the chart shown below. In Step 5, set the Row Source property to Table1.

The completed Bar Chart, created using the sample data above, illustrates the quarterly performance of individual areas—Revenue, Expenses, and Income—and is shown below.

Click to Enlarge

Customizing Chart Y-axis Scale Values

The Y-axis scale of the chart, along with the major unit intervals (e.g., 0, 5000, 10000), is automatically calculated and displayed by MS Access. However, when the chart data contains smaller values, it may be necessary to adjust these intervals for better visibility. In such cases, you can manually customize the Y-axis scale to use smaller unit intervals as needed.

To adjust the Y-axis scale manually, double-click on the chart to enter edit mode. Then, right-click on the Y-axis (the vertical line displaying the scale values) and select Format Axis from the shortcut menu. In the dialog box that appears, go to the Scale tab and modify the values as needed. For example, you can change the Minimum, Maximum, or Major Unit settings to better suit your chart data.

• Minimum = 0
• Maximum = 51000
• Major Unit = 3000

Click to Enlarge

Leave the other values unchanged. Click OK to update the new scale settings on the Chart.

Once you manually change the Y-axis scale settings, they will remain fixed, even if the actual chart values exceed the defined maximum. In such cases, you must update the maximum value manually to ensure all data points are displayed correctly. Alternatively, you can enable automatic scaling by checking all relevant options in the Scale tab, allowing MS Access to recalculate and adjust the scale values dynamically as the data changes.

Formatting Data Labels.

You can adjust the alignment of the chart’s data labels to improve readability. Right-click on any label and choose Format Data Labels from the shortcut menu. Go to the Alignment tab and select one of the diamond-shaped icons under Orientation to change the label direction. Feel free to experiment with the other alignment options to find the best fit for your chart layout.

You can display the actual data table used to generate the chart alongside the chart itself. To do this, double-click on the chart to enter edit mode. Then, right-click in an empty area outside the plot area and select Chart Options. Navigate to the Data Table tab and check the Show Data Table option.

Click to Enlarge

Secondary Y-axis Usage.

Sometimes, we need to display smaller values alongside much larger ones on the same chart. For example, if the income values for all four quarters are below 3000, their bars or lines (in a line chart) may appear too small, making it difficult to compare them effectively.

In such scenarios, using a Secondary Y-axis allows you to scale smaller values independently, enhancing the visibility of bars or lines representing those values. Including data labels further improves clarity. The sample image below illustrates this, with Income values (colored bars) plotted on the secondary Y-axis.

Click to Enlarge

Adjusting Bar Width.

To reduce the thickness of the blue bars and make them as narrow as the other bars, we need to increase the gap between them. Double-click the chart to enter edit mode, then right-click one of the blue bars and select Format Data Series. On the Options tab, set the Gap Width value to 340, and click OK to apply the changes to the chart.

The Image of a Chart plotted with the same values in Custom Chart Type Tubes is given below:

Click to Enlarge

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Objects in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More

MS-Access and Graph Charts

Access Reports and Graph Charts.

Access Reports are a powerful tool for presenting information in both numbers and text. However, Graph Charts take it a step further by conveying data visually, often revealing insights at a glance. With a basic understanding of how charts work, you can easily create them in Access. The key principle is to illustrate the progression or change of an event over time using dots and lines to represent values, instead of relying solely on raw numbers or text.

Before creating graphs in MS Access, it's essential to understand how to structure your data appropriately. To get familiar with this process, we'll explore a few examples that use a single set of values and demonstrate the required data format for generating meaningful graphs.

Events such as daily temperature changes, fluctuations in a patient's body temperature (for health monitoring in hospitals), or variations in gold prices over time can all be effectively visualized using line or bar charts. Traditionally, these are plotted manually on graph paper by marking each data point according to a fixed scale, starting from zero at the bottom of the Y-axis (vertical axis), which is calibrated based on the highest expected value.

Each day's data point is plotted as a dot along the Y-axis, aligned with the corresponding date on the X-axis (horizontal axis). These dots can be connected with lines to show trends, and the actual values can be labeled near each dot. A legend is typically included to describe what the graph represents (e.g., Temperature).

Instead of using paper and pencil, we can achieve the same results—more efficiently and professionally—using the Microsoft Graph Chart object in MS Access.

Preparing for a simple Chart.

We plan to monitor the daily Stock Index, and the values are recorded in an Access Table as given below.

Desc	Date	Val
Stock Index	01-08-07	1245
Stock Index	02-08-07	1455
Stock Index	03-08-07	1395
Stock Index	04-08-07	1575
Stock Index	05-08-07	1125

 

OR

 

Desc	01-08-07	02-08-07	03-08-07	04-08-07	05-08-07
Stock Index	1245	1455	1395	1575	1125

Right-click to open a Large Image in New Tab/Window

When the above data is plotted in the form of Charts, the contour of the Charts shows the trend of the stock index at a glance rather than reading the numbers and comparing them.

Before preparing the data for the Chart, we must decide which type of Chart is best suited for the data.

For example, if we want to present a chart showing the company's total revenue from various sources—such as Vehicle Sales, Parts Sales, and Service Sales—and highlight each category's contribution to the overall total, a Pie Chart would be the ideal choice.

However, if we want to display the monthly performance of each category separately over time, a Line Chart or Bar Chart would be more appropriate, as they are better suited for showing trends and comparisons across periods.

If the source data for a chart changes at regular intervals—such as every month—then the underlying Table or Query should be designed to automatically reflect the updated data. This ensures that the chart dynamically displays the latest information without requiring manual modifications each time the data changes.

Charts in Excel and MS Access.

Creating charts in Access isn't as straightforward as it is in Excel. In this section, we’ll explore the most commonly used chart types in Access: Line, Bar, and Pie charts. If you're familiar with Excel charts, you might initially find Access’s data preparation methods a bit challenging. However, once you get past the learning curve, you will find Access charts just as powerful—and might not feel the need to return to Excel at all.

However, unlike Excel, Access offers the advantage of using various types of Queries and Macros for automating the chart data preparation process. This allows us to extract and present key figures from large volumes of data quickly and efficiently, regardless of the chart type. Once the chart setup is complete, it can run consistently without requiring manual intervention.

Note: You can enhance charting capabilities in Access by attaching the Microsoft Excel Object Library to the Access database's list of References. This enables a wide range of Excel’s chart types and advanced formatting options directly within Access.

Line and Bar Charts.

The Sample Source Data presented in the first format above must be changed into the 2nd format to create both Line and Bar Charts. In Excel, both formats are valid. With a Cross-Tab Query, we can change the data format for our Chart.

1. Create a Table with the Field Names Desc, Date, and Val, and save it as Stock1.

2. Key in the six records from the sample data above, as we normally maintain data in Access Tables.

3. Display the SQL Window of a new Query (don't select a table or Query from the list displayed), copy and paste the SQL String of a Cross-Tab Query given below into the SQL Window, and save it with the name BarChartQ

   TRANSFORM Sum(Stock1.Val) AS SumOfVal
   SELECT Stock1.Desc
   FROM Stock1
   GROUP BY Stock1.Desc
   PIVOT Stock1.Date;

4. Open a new Report in the design view. Select Object... from the Insert Menu (Chart Option on the View Menu uses only six Fields for a Chart), select Microsoft Graph Chart, and click OK. A Chart Object with default values is inserted into the Report.

5. Click outside the chart in the report to deselect the chart from Edit mode. Click again on the chart to select it, display the property sheet, and change the following values:

   • Size Mode = Zoom.
   • Row Source Type = Table/Query
   • Row Source = BarChartQ
   • Column Heads = Yes
   • Left = 0.3"
   • Top = 0.3"
   • Width = 6.0"
   • Height = 4.0"

6. Double-click on the Chart. The Chart Formatting Toolbar will appear at the top. Select Column Chart from the Chart Type Toolbar Options, or Right-Click on an empty area within the Chart and select Chart Type from the Shortcut Menu, and select Column Chart.

   

   Right-click to open a Large Image in New Tab/Window

   In the design view, the Chart object displays a chart with sample data in an Excel-like data Sheet (if not visible, double-click on the chart). But when the Report is print-previewed, the Chart will appear with the actual data from the Table/Query we have attached as the Source. To see the result of the Source Data in Design View, also copy and paste the Source Data into the Excel-like Cells.

   Minimize the Report you're working on and switch to the Queries tab.
   Double-click BarChartQ to open it.
   In the Datasheet view, click the top-left corner to select all records, then click the Copy button (or choose Edit > Copy).

   Restore the Report containing the Chart and double-click the Chart to open its Data Grid.
   Again, click the top-left corner of the Grid to select all cells, and paste the copied data (use the Paste button or Edit > Paste).

   To remove any extra rows, click the left border of the unwanted lines below the data and choose Edit > Cut.

   The Chart in Design View will now reflect the actual data values.

   Chart Formatting for a Visually Pleasing Look.

   We will format the Chart for better appearance.

7. Removing the Plot Area Shading for a Cleaner Look.

   If the Chart is still open after pasting the data into the Data Grid (or double-click the Chart to enter editing mode), follow these steps:

   1. Right-click at the center of the chart, specifically in the shaded background away from the chart bars or lines.

   2. When the Plot Area is highlighted, select Format Plot Area… from the shortcut menu.

   3. In the Format Plot Area dialog:

      • Go to the Area Options section.

      • Select the None option to remove the background fill.

   4. Click OK to apply the changes and close the dialog.

   This will remove the shading behind your data series, resulting in a cleaner, more professional-looking chart.

8. Right-click one of the Bars, select Format Data Series, click on the Fill Effect Button, and select the Gradient Tab.

9. Click Vertical under Shading Styles, click the Right Bottom Style among the four Styles displayed. If you want to change the Color of the Bars, you can select the color from the color palette. Accept the default one-color option without change.

10. Select the Data Labels Tab. Put a check mark in the Value Option, click the OK button to reflect the change on the Graph.

11. If the Data Label Font size is large, then right-click on one of the labels and select Font, Font Style, Size, etc., from the Menu, and change the values to your liking (say 10 points). Repeat the same change in X-Axis Labels, Y-Axis Labels, and Legend.

12. Right-click on the Chart outside the Plot Area and select Chart Options.

13. Type NYSE Index in the Chart Title Control.

14. Save the Report with the Chart and open it in Print Preview.
    

    Right-click to open a Large Image in New Tab/Window

15. Add two more records to the Chart's Table Stock1. Print Preview the Report with the Chart again and check whether the newly added records are appearing as new Bars on the Chart.

Creating a Line Chart from the Existing Bar Chart

You can easily create a Line Chart using the same data as your Bar Chart by duplicating and modifying it:

1. In Design View, click on the existing Bar Chart to select it.

2. Press Ctrl+C to copy, then Ctrl+V to paste a duplicate chart below the original.

3. Double-click on the new chart to open it for editing.

4. Right-click on an empty space inside the chart area (but outside the plot area) to bring up the shortcut menu.

5. Choose Chart Type from the menu.

6. In the list of available chart types, select Line Chart and click OK.

Now, your new chart will display the same data in a line chart format.

1. MS-Access and Graph Charts
2. MS-Access and Graph Charts-2
3. Working With Chart Objects in VBA
4. Column Chart and VBA
5. Pie Chart Object and VBA
6. Missing Lines in Line Chart
7. Pie Chart and Live Data on Form
8. Scaling Chart Object
9. Cross-Tab Union Queries for Chart

Read More