Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Chart Via Code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Chart Via Code
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("qryUserPrepReadinessBySit eAndFu").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 Whe=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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamic Chart Via Code
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" wrote in message ... 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("qryUserPrepReadinessBySit eAndFu").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 Whe=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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart- curve dropping to zero ( chart type- Line) | Charts and Charting in Excel | |||
Dynamic Chart Title With 3D Stacked Column Chart | Charts and Charting in Excel | |||
Dynamic chart pasted to a new workbook in report can't be dynamic | Charts and Charting in Excel | |||
TIP: Dynamic chart selection without code | Charts and Charting in Excel | |||
Create a dynamic chart with code | Excel Programming |