Dynamic Chart Via Code

[cherman]

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

 

[Tom Ogilvy]

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.

 

[cherman]

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

 

[Tom Ogilvy]

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

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)