Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Working with Chart Object in VBA

Introduction.

Working with Microsoft Access Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it can be used in other Access Databases to prepare Access Graph Charts with the same look and feel in minutes. 

There are several Chart Types, Chart Areas, and numerous Object Groups and Objects to work with. The Range of values that can go into them is too many. There are Access Chart Type-specific properties and value ranges, like the Auto Scaling property that can be used only for 3D Charts.

Note: But first, please ensure that you have attached the following important Reference Library Files to your Database, in order to work with VBA Code without running into Errors.:

Attaching System Reference Library Files

Display the Visual Basic Editor window, by selecting the Visual Basic Editor Option from Tools Menu or Code from View Menu.

Select the References from Tools Menu. Put the check mark on the following Library Files in the Available References Dialogue Control:

  1. Microsoft Office 12.0 Object Library
  2. Microsoft Excel 12.0 Object Library

These are also necessary when you run Options in the Demo Database, attached for Downloading at the end of this Page.

I did some searching, and experimenting, and created a VBA Routine that can modify the Access Graph Chart Elements with beautiful formatting, to my liking. I hope you will like it too. The sample run result image is given below.

Formatting Chart Elements

The Resizing of the Graph Chart and formatting of several elements, like Chart Title, Chart Axis Titles, Chart Area, Chart PlotArea, and Chart Bars with Gradient color, Legends, Grid Lines, Data Labels, and alignment of Data Labels are done with VBA Code. Placing a Chart Object on the Report with few property changes, as a starter kit, is done manually.

When the Program is run, it gives three options to choose from:

  1. Bar Chart
  2. Line Chart
  3. Pie-chart.

The same Graph Chart is transformed into any one of these Types and the sample values from a table are used for all three. Pie-Chart will only use the first record from the Table and the treatment of values in the pie-chart is different from Bar Chart or Line Chart. Here, it is used only for example purposes, and Pie-chart is not treated separately.

Color Numbers

The following Color index numbers can be used in different combinations in the Program to get different shades of gradients.

Preparing for Trial Run

Follow the few simple steps explained below to get prepared for the trial run.

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

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

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

  5. Click outside the chart on the Detail Section of the Report to de-activate and de-select 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) then resizing the Chart with the last four values (Left, Top, Width & Height given above) will only change the size of the Outer Frame. The inner Sections' Chart Area and Plot Area will not resize with the change. If it is set to Stretch or Zoom(Values 1 or 3 respectively) then they will resize to fit within the outer frame area. Zoom is a better setting because it will maintain the Chart in the correct aspect ratio when re-sized, without distortion.

    The Column count property value is found from the Row Source Table or Query String.

    We are validating the settings in the Row Source Type property. If it is not set with the value Table/Query then it will change to that value. But, if the Row Source property is not set with a Table Name or valid SQL String then the program will terminate with a message.

  8. Save the Report with the Name myReport1.
  9. The VBA Code.

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

  11. Open the VBA Module that you have pasted the code if you have closed it.
  12. 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 On_Click() Event Procedure of a Command Button on a Form.

  13. 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, modifies the Graph Chart for the selected Type, Saves it, and then Re-opens 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 the design view or Print Preview.

When you run the Code for Bar Chart or Line Chart, the Gradient Scheme Color Codes are selected Randomly, which will give 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
Share:

10 comments:

  1. I'm receiving a compile error "Expected variable or procedure, not project" that highlights "Reports" from the following that I copied/edited in my database (I have MS Access 2003). What am I missing? Do you know why I am receiving this error?
    -------------------
    Dim Rpt As Report
    Dim grphChart As Object
    Dim ReportName As String
    Dim ChartObjectName As String

    ReportName = "rptHours"
    ChartObjectName = "GraphHours"

    DoCmd.OpenReport ReportName, acViewDesign
    Set Rpt = Reports(ReportName)
    Set grphChart = Rpt(ChartObjectName)
    grphChart.Activate

    ReplyDelete
  2. Ensure that you have not given the Global Module Name same as your Database, which I think it is now. The Procedure Name also should not have conflict with these names. You have to make change in one more of these values.

    Open the VBA Module. Select Tools - -> yourdatabasename Properties....

    Check the Project Name (which will be same as of your database name) and change it to something else than the Module Name.

    After changes re-compile the Code and Run the Program.

    ReplyDelete
  3. I get a compile error on msoGradientVertical and msoGradientHorizontal. One other example I saw used the Points object with these constants, not SeriesCollection object.

    ReplyDelete
  4. Attach Microsoft Office Library File to your Project. Open any VBA Module. Select Tools - -> References and put a check mark on Microsoft Office 9.0 Library or equalent Version available on your machine.

    Refer the Post: http://www.msaccesstips.com/2006/09/command-button-animation/ for more details of attaching a Library File to your Project.

    Regards,

    ReplyDelete
  5. I am getting a type error at grphChart.Activate or grphChart.chartType = lngType
    I am using Access 2007. I have tried everything I can think of, but must be missing something simple. Enlightenment would be much appreciated!

    ReplyDelete
  6. A sample database with the Chart Object examples will be prepared and added to the Download section shortly. You may download it. If you can leave your email address I shall forward a copy to you when it is ready.

    Regards,
    a.p.r. pillai

    ReplyDelete
  7. I am getting Type mismatch error when I try this and no changes appear to be made to the pie chart?
    I have MS Office Pro 2003 SP3, I created the chart myself and then created a module called Charts with your code pasted in. I ran from the immediate window...
    Thanks

    ReplyDelete
  8. You may Download a Sample Database with running version of all examples of Charts from the following Link:
    www.msaccesstips.com/downloads/2008/06/workingwith_chartobject.shtml

    Regards,
    a.p.r. pillai

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code