Dynamic Chart Via Code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to create a dynamic chart via code and cannot get it to work.
Here is some sample data from the sheet that is created:

Site Function Attendance Trainer Raiting Asmt 1 Asmt 2 Asmt 3
S1 F1 1 1.4 0 1.1 0 0
S1 F2 0 0 0 2 0 0
S1 F3 1 1.45 0 1.5 0 0

There could be any number of rows, but always the same # of columns. And
there will always be at least 1 row.

I want a simple bar graph with the title being "TEMPE ASSESSMENT TRAINER".
The position and the size of the chart to be Left:=390, Width:=300, Top:=5,
Height:=200. The X values from 0.00 to 3.5 at .5 intervals. The function to
be the Category axis at the bottom. The Trainer column to be the Series 1
data.

That's all I can think of. I want to create the chart via code. I can get
the data to the sheet, the sheet opened, etc. I just cannot get the code to
create this chart.

Thanks in advance,
Clint
 
Turn on the macro recorder while you create the chart manually. Then turn it
off and look at the code. this should give you 90% of the solution.
 
Well, I don't usually like the code that is generated via macro, but I went
ahead and did it anyway. I've included slightly modified code below, which
craps out at the 4th line with this error:

"Methods 'Sheets' of object '_Global' failed"

I figured what I wanted was simple enough that someone with the right
experience would be able to throw up in a few minutes. Here is the code that
I am trying to use. I just do not have the practice working with this kind of
VBA. Oh, and I had to modify the code because I am calling this from within
Access.

Dim myChtObj As ChartObject

Set myChtObj =
objActiveWkb.Worksheets("qryUserPrepReadinessBySiteAndFu").ChartObjects.Add(Left:=390, Width:=300, Top:=5, Height:=200)

myChtObj.Chart.ChartType = xlColumnClustered

myChtObj.SetSourceData
Source:=Sheets("qryUserPrepReadinessBySiteAndFu").Range("A1:G4"),
PlotBy:=xlRows

myChtObj.SeriesCollection.NewSeries

myChtObj.SeriesCollection(1).XValues =
"=qryUserPrepReadinessBySiteAndFu!R2C2:R4C2"

myChtObj.SeriesCollection(1).Values =
"=qryUserPrepReadinessBySiteAndFu!R2C4:R4C4"

myChtObj.SeriesCollection(1).Name =
"=qryUserPrepReadinessBySiteAndFu!R1C4"

myChtObj.Location Where:=xlLocationAsObject,
Name:="qryUserPrepReadinessBySiteAndFu"

With myChtObj.Axes(xlCategory)
.HasMajorGridlines = False
.HasMinorGridlines = False
End With

With myChtObj.Axes(xlValue)
.HasMajorGridlines = True
.HasMinorGridlines = False
End With

myChtObj.HasLegend = False

myChtObj.HasDataTable = False

I also have to consider the fact that I need a dynamic chart - one that will
allow for any # of rows. I'm pretty sure that the code generated via macro
will not give this, so that's another change I will need to incorporate.

Any help with getting this to work would be most appreciated.

Much thanks!
Clint Herman
 
Sites with strong information on VBA and Charts:

Jon Peltier
http://www.peltiertech.com/index.html

John Peltier's site
http://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html#VBAcharts

Tushar Mehta
www.tushar-mehta.com

Andy Pope
http://www.andypope.info

Debra Dalgleish
http://www.contextures.com/tiptech.html

Articles with SAMPLE Code:

http://support.microsoft.com/default.aspx?scid=kb;en-us;157940&Product=xlw
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default.aspx?scid=kb;en-us;213780&Product=xlw
XL2000: How to Use Visual Basic to Create a Dynamic Chart

http://support.microsoft.com/default.aspx?scid=kb;en-us;146055&Product=xlw
Using Visual Basic to Create a Chart Using a Dynamic Range


--
Regards,
Tom Ogilvy



cherman said:
Well, I don't usually like the code that is generated via macro, but I went
ahead and did it anyway. I've included slightly modified code below, which
craps out at the 4th line with this error:

"Methods 'Sheets' of object '_Global' failed"

I figured what I wanted was simple enough that someone with the right
experience would be able to throw up in a few minutes. Here is the code that
I am trying to use. I just do not have the practice working with this kind of
VBA. Oh, and I had to modify the code because I am calling this from within
Access.

Dim myChtObj As ChartObject

Set myChtObj =
Click to expand...
objActiveWkb.Worksheets("qryUserPrepReadinessBySiteAndFu").ChartObjects.Add(
Left:=390, Width:=300, Top:=5, Height:=200)
 
Back
Top