Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 793
  • Last Modified:

Type error with MS Graph Chart Objects


Hi,

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:  www.msaccesstips.com/2008/07/column-chart-and-vba.shtml.

Thanks!

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 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
rst.Close
 
With grphChart
    .ColumnCount = colmCount
    .SizeMode = 3
    .Left = 300
    .Top = 250
    .Width = 5000
    .Height = 4000
End With
 
grphChart.Activate
 
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

0
klwAtMich
Asked:
klwAtMich
  • 3
  • 2
2 Solutions
 
Jeffrey CoachmanMIS LiasonCommented:
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:
aprpillai@msaccesstips.com

JeffCoachman
0
 
klwAtMichAuthor Commented:
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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Make sure you set a reference to the MSGraph Library in your VBA editor:

Open the VBA editor and click:
Tools-->References

Jeff
0
 
klwAtMichAuthor Commented:
As per my request, the author posted a sample database.  It can be downloaded at:
www.msaccesstips.com/downloads/2008/06/workingwith_chartobject.shtml

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.


ReferenceLibraries.jpg
0
 
Jeffrey CoachmanMIS LiasonCommented:
Ok
Glad I was able to help
;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now