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

This is the code I wish to use to create a bar chart and do specific things to it. However, it keeps aborting on "Chart 8" because the next time I use the macro, it becomes "Chart 9."

How would I add VBA code to make the chart number (and row number) increase each time I use the routine?

Thanks

Jeff

Sub OATBarChart()
'
' OATBarChart Macro
'

'
Range("E1:J2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'OAT Test Charts Data_Crosstab'!$E$1:$J$2")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.HasAxis(xlValue) = True
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 1
ActiveChart.Axes(xlValue).MajorUnit = 0.1
ActiveChart.Axes(xlValue).MajorUnit = 0.2
Selection.TickLabels.NumberFormat = "0.00%"
Selection.TickLabels.NumberFormat = "0%"
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleVertical)
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveSheet.ChartObjects("Chart 8").Activate
End Sub
  #2   Report Post  
Posted to microsoft.public.excel.charting
external usenet poster
 
Posts: 1,549
Default Help with VBA code

I set up an object reference to the newly added chart and used that in the code.
That was done by declaring the "chtNew" variable as a chart: Dim chtNew As Chart
and by telling Excel what it referred to: Set chtNew = ActiveChart
'----------
Sub OATBarChart_R1()
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'!$E$1:$J$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"
End Sub
'---------
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)









"Jeffrey Marks"
wrote in message
...
This is the code I wish to use to create a bar chart and do specific things to it.

However, it keeps aborting on "Chart 8" because the next time I use the macro, it becomes "Chart 9."

How would I add VBA code to make the chart number (and row number) increase each time I use the
routine?
Thanks
Jeff

Sub OATBarChart()
'
' OATBarChart Macro
Range("E1:J2").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range( _
"'OAT Test Charts Data_Crosstab'!$E$1:$J$2")
ActiveChart.ChartType = xlColumnClustered
ActiveChart.Legend.Select
Selection.Delete
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.HasAxis(xlValue) = True
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).Select
ActiveChart.Axes(xlValue).MinimumScale = 0
ActiveChart.Axes(xlValue).MaximumScale = 1
ActiveChart.Axes(xlValue).MajorUnit = 0.1
ActiveChart.Axes(xlValue).MajorUnit = 0.2
Selection.TickLabels.NumberFormat = "0.00%"
Selection.TickLabels.NumberFormat = "0%"
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleVertical)
ActiveChart.SetElement (msoElementChartTitleAboveChart)
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartTitle.Text = "Adams County - 8th Grade Mathematics Results"
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.ChartArea.Select
ActiveSheet.ChartObjects("Chart 8").Activate
ActiveChart.Axes(xlValue).AxisTitle.Select
ActiveSheet.ChartObjects("Chart 8").Activate
End Sub



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 05:51 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"