Dynamic Chart Via Code

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.

--
Regards,
Tom Ogilvy


"cherman" wrote:

> 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

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" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "cherman" wrote:
>
> > 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

Sites with strong information on VBA and Charts:

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

John Peltier's site
http://peltiertech.com/Excel/ChartsH...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...40&Product=xlw
XL97: How To Create a Dynamic Chart Using Visual Basic

http://support.microsoft.com/default...80&Product=xlw
XL2000: How to Use Visual Basic to Create a Dynamic Chart

http://support.microsoft.com/default...55&Product=xlw
Using Visual Basic to Create a Chart Using a Dynamic Range


--
Regards,
Tom Ogilvy



"cherman" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "cherman" wrote:
> >
> > > 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