Type error with MS Graph Chart Objects

Posted on 2009-05-14
Last Modified: 2013-11-27


I am trying to run a vba chart code example  for MS Access I found by a.p.r. pillai.  I get a "type error" at grphChart.Activate or .chartType = xlColumnClustered.   (see relevant portion of code below).  I feel like I have tried everything, but am very confused about how MS Graph Chart works as an object in Access...  Any enlightenment as to where I am going wrong would be greatly appreciated.  

The full example cam be found at:


Public Function ColumnChart(ByVal ReportName As String, _

ByVal ChartObjectName As String)


'Author : a.p.r. pillai

'Date   : June-2008

'URL    :

'Customized Source Code : from Microsoft Access Help


Dim Rpt As Report, grphChart As Object

Dim db As Database, rst As Recordset, recSource As String

Dim colmCount As Integer

lngType = 

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


With grphChart

    .ColumnCount = colmCount

    .SizeMode = 3

    .Left = 300

    .Top = 250

    .Width = 5000

    .Height = 4000

End With


With grphChart

     .chartType = xlColumnClustered

     .RightAngleAxes = True

     .AutoScaling = True

     .HasLegend = True

     .HasTitle = True

    .ChartTitle.Font.Name = "Verdana"

    .ChartTitle.Font.Size = 14

    .ChartTitle.Text = chartType(typ) & " Chart."

    .HasDataTable = True

    .ApplyDataLabels xlDataLabelsShowValue

End With

Open in new window

Question by:klwAtMich
  • 3
  • 2
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24392612
As far as I can tell, these are all charts that can be created with the Access Chart Wizard *without* using VBA code.

If you need help creating the basci chart using the Access chart wizard, let me know, and post a sample of your data and I will help you through it.

On the other hand, if you need help with this specific code, I suggest you contact the author who wrote it:


Author Comment

ID: 24394988
Thanks, boaq2000,

I know I can create these graphs by hand.  I am just trying to figure out how on earth one WOULD edit a chart using VBA,  so I can figure out how to edit that chart of mine.  I did contact the author, but who knows how long it will take to get a response, and I need to finish this project today.  

I figured someone here must know how to edit charts with VBA. I see that you are strongly opposed to the idea, but I really don't see how my labels are going to work otherwise.

It looks like I may just have to go back to cutting and pasting the query into the Excel sheet each time, because I've just spent 2 days on this and seem to be getting nowhere...  

Thanks for trying to help.
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 24399279
Make sure you set a reference to the MSGraph Library in your VBA editor:

Open the VBA editor and click:


Accepted Solution

klwAtMich earned 0 total points
ID: 24825611
As per my request, the author posted a sample database.  It can be downloaded at:

This allowed me to see where I was going wrong.  boaq2000 was on to something, although the problem was not the MSGraph Library, but a set of additional libraries that must be turned on with it (and some that I had on that conflicted with others that needed to be on).  I am not sure which exact ones are essential here, but the set in the attached image seem to do the trick to allow one to edit MSChart Objects in VBA.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24859296
Glad I was able to help

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now