#1   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 12
Default Help with VBA code

Jim

Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I also put the ChartTitle in Column D

I've been trying to research a way to do this for bar charts on-line, but haven't found anything applicable. I know I need a loop and a counter, but not sure of where to go to find an example of cluster columns.

Jeff


Sub OATChartCreate()

Dim chtNew As Chart '<<<
ActiveCell.Resize(2.6).Select
ActiveSheet.Shapes.AddChart.Select
Set chtNew = ActiveChart '<<<
chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
chtNew.ChartType = xlColumnClustered
chtNew.Legend.Delete
chtNew.HasAxis(xlValue) = True
chtNew.Axes(xlValue).MinimumScale = 0
chtNew.Axes(xlValue).MaximumScale = 1
chtNew.Axes(xlValue).MajorUnit = 0.1
chtNew.Axes(xlValue).MajorUnit = 0.2
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
ActiveChart.ChartArea.Select
chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
chtNew.SetElement (msoElementChartTitleAboveChart)
chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,549
Default Help with VBA code

You sound like a candidate for "Sparklines" - a new feature in XL2010.
Some examples shown here...
http://blogs.office.com/b/microsoft-...-in-excel.aspx
'---
Also,
Have you looked in Excel VBA help for "AddChart" found in this line in the code:
"ActiveSheet.Shapes.AddChart.Select"
Note: In VBA, a dot "." is used to join distinct parts of the code.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Formats & Styles xl add-in: lists/removes unused styles & number formats - in the free folder)



"Jeffrey Marks"
wrote in message
...
Jim

Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does
this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need
a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I
also put the ChartTitle in Column D

I've been trying to research a way to do this for bar charts on-line, but haven't found anything
applicable. I know I need a loop and a counter, but not sure of where to go to find an example of
cluster columns.

Jeff


Sub OATChartCreate()

Dim chtNew As Chart '<<<
ActiveCell.Resize(2.6).Select
ActiveSheet.Shapes.AddChart.Select
Set chtNew = ActiveChart '<<<
chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
chtNew.ChartType = xlColumnClustered
chtNew.Legend.Delete
chtNew.HasAxis(xlValue) = True
chtNew.Axes(xlValue).MinimumScale = 0
chtNew.Axes(xlValue).MaximumScale = 1
chtNew.Axes(xlValue).MajorUnit = 0.1
chtNew.Axes(xlValue).MajorUnit = 0.2
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
ActiveChart.ChartArea.Select
chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
chtNew.SetElement (msoElementChartTitleAboveChart)
chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub


  #3   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 31
Default Help with VBA code

On Jul 12, 8:43*am, Jeffrey Marks wrote:
Jim

Thank you! This works great. I have to do this for 100+ charts and hoped to create 1 macro that does this for each row. Row 1 has the labels in the previous example and Row 2 has the data. I would need a counter to increase by 1 (and not include Row 2 in Row 3's chart, etc.) In preparation for this, I also put the ChartTitle in Column D

*I've been trying to research a way to do this for bar charts on-line, but haven't found anything applicable. I know I need a loop and a counter, but not sure of where to go to find an example of cluster columns.

Jeff

Sub OATChartCreate()

*Dim chtNew As Chart * * * * * * * '<<<
* * ActiveCell.Resize(2.6).Select
* * ActiveSheet.Shapes.AddChart.Select
* * Set chtNew = ActiveChart * * '<<<
* * chtNew.SetSourceData Source:=Range("'OAT Test Charts Data_Crosstab'!$F$1:$K$2")
* * chtNew.ChartType = xlColumnClustered
* * chtNew.Legend.Delete
* * chtNew.HasAxis(xlValue) = True
* * chtNew.Axes(xlValue).MinimumScale = 0
* * chtNew.Axes(xlValue).MaximumScale = 1
* * chtNew.Axes(xlValue).MajorUnit = 0.1
* * chtNew.Axes(xlValue).MajorUnit = 0.2
* * chtNew.Axes(xlValue).TickLabels.NumberFormat = "0.00%"
* * chtNew.Axes(xlValue).TickLabels.NumberFormat = "0%"
* * ActiveChart.ChartArea.Select
* * chtNew.SetElement (msoElementPrimaryValueAxisTitleVertical)
* * chtNew.SetElement (msoElementChartTitleAboveChart)
* * chtNew.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
* * chtNew.Axes(xlValue, xlPrimary).AxisTitle.Text = "Percent Passing"
End Sub


Take a look at the Chart Assistant, http://www.add-ins.com/chart_assistant.htm.
It is designed to create one chart per row of data and allows you to
customize the charts.

Robert Flanagan
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
split post code (zip code) out of cell that includes full address Concord Excel Discussion (Misc queries) 4 October 15th 09 06:59 PM
Code to conditional format all black after date specified in code? wx4usa Excel Discussion (Misc queries) 3 December 26th 08 08:06 PM
Drop Down/List w/Code and Definition, only code entered when selec Spiritdancer Excel Worksheet Functions 2 November 2nd 07 04:57 AM
Convert a Number Code to a Text Code Traye Excel Discussion (Misc queries) 3 April 6th 07 09:54 PM
copying vba code to a standard code module 1vagrowr Excel Discussion (Misc queries) 2 November 23rd 05 05:00 PM


All times are GMT +1. The time now is 02:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"