Working with Chart Object in VBA is a complicated matter, but interesting too. Once the code is developed it is very useful and can be used across Applications to prepare 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, are too many. There are Chart Type specific properties and value-ranges, like the Auto Scaling property that can be used only for 3D Charts.

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

Resizing of Graph Chart and formatting of several elements, like Chart Title, Axis Titles, Chart Area, Plot Area 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, are 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 is used for all the three. Pie-Chart will only use the first record from the Table and the treatment of values in a 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.

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

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

    

   • SizeMode : 3 (Zoom)
   • ColumnCount : Number of Colums in the Source Data
   • Left : 0.2917?
   • Top : 0.2708?
   • Width : 5.5729?
   • Height : 4.3854?

    

   A 2D Chart Object has 3 different areas:

    

   1. the outer Frame
   2. Chart Area where Chart Title, Axis Titles and Legend Values appears.
   3. Plot Area where the chart Data Series Values, Data Labels, Gridlines, Scale and Category Values appear in Bar or Line Charts.

    

   If the SizeMode property 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 proportions 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 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. 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
   '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" & crmsg = msg & "2. Line Chart" & crmsg = msg & "3. Pie Chart" & crmsg = msg & "4. Quit" & crmsg = 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 = xl3DColumnClustered
       '.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
   
   '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
   

 

10. Open the VBA Module that you have pasted the Code, if you have closed it.

 

11. Run the code from the Debug Window (press Ctrl+G to display 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.

     

12. Select the 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 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 Demo Database