|
Chart VBA
Jimmy wrote:
Thanks for getting back to me, Andy. ------------------------------------------------------------------------------------------------------------- XL02 ------------------------------------------------------------- In this case topright = E5 and bottomleft = Q9 The goal is for Row6 to be plotted on the primary axis and for Rows 7-9 to be plotted on the secondary access. Here is a smattering of the chart data I use: E F G H I 5 Mar-00 Apr-04 Jun-04 Apr-05 6 # of Bids/Quotes Received 768 117 1,050 172 7 % Submitted of Received 17.2% 8.5% 9.6% 19.2% 8 % Won to Date of Submitted 66.8% 2.5% 5.9% 8.2% 9 % Won to Date of Received 1.5% 2.0% 4.8% 4.3% ------------------------------------------------------------------------------------------------------------ Another potential problem here is that my enduser has requested a custom-type chart called "Lines on Two Axes" and I am unfamiliar with how to code for this type of chart I am able to create this chart manually. The "record" code in concert with my lack of experience with this type of chart is a problem. Again, thanks- JR --------------------------------------------------------------------------------------------------------------------------- Andy Pope wrote: Hi, Does the range defined by topleft and bottomright have enough columns to have series placed on the secondary axis? I would imagine trying to set the properties of the secondary axis that does not exist would cause problems. Cheers Andy Jimmy wrote: Hi, I am creating a chart in Excel. At lines 15-17 (see below) I get "Method 'Axes' of Object 'Chart_Chart' failed." Any help in getting this chart to create would be greatly appreciated. Thanks, Jimmy 2 Charts.Add 3 ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ 4 "Lines on 2 Axes" 5 ActiveChart.SetSourceData Source:=Sheets("Chart").Range(topLeft & ":" & bottomRight), PlotBy:= _ 6 xlRows 7 ActiveChart.Location Whe=xlLocationAsObject, Name:="Chart" 8 With ActiveChart 9 .HasTitle = True 10 .ChartTitle.Characters.Text = "Bid/Quote Success Rate" 11 .Axes(xlCategory, xlPrimary).HasTitle = False 12 .Axes(xlValue, xlPrimary).HasTitle = True 13 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ 14 "# of Bids/Quotes Received" 15 ' .Axes(xlCategory, xlSecondary).HasTitle = False 16 ' .Axes(xlValue, xlSecondary).HasTitle = True 17 ' .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = _ 18 "% Submitted of Received or % Won to Date of Submitted/Received" 19 End With -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Chart VBA
Hi,
This code will produce your chart. Don't worry about using the built-in custom type. You can create the same thing yourself. Sub Jimmy() topleft = "E5" bottomright = "I9" Charts.Add ActiveChart.SetSourceData Source:=Sheets("Chart").Range( _ topleft & ":" & bottomright), PlotBy:=xlRows ActiveChart.Location Whe=xlLocationAsObject, Name:="Chart" With ActiveChart .ChartType = xlLine .HasTitle = True .ChartTitle.Characters.Text = "Bid/Quote Success Rate" ' move to secondary axis For lngseries = 2 To .SeriesCollection.Count .SeriesCollection(lngseries).AxisGroup = 2 Next .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "# of Bids/Quotes Received" .Axes(xlCategory, xlSecondary).HasTitle = False .Axes(xlValue, xlSecondary).HasTitle = True .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = _ "% Submitted of Received or % Won to Date of Submitted/Received" End With End Sub Cheers Andy Jimmy wrote: Jimmy wrote: Thanks for getting back to me, Andy. ------------------------------------------------------------------------------------------------------------- XL02 ------------------------------------------------------------- In this case topright = E5 and bottomleft = Q9 The goal is for Row6 to be plotted on the primary axis and for Rows 7-9 to be plotted on the secondary access. Here is a smattering of the chart data I use: E F G H I 5 Mar-00 Apr-04 Jun-04 Apr-05 6 # of Bids/Quotes Received 768 117 1,050 172 7 % Submitted of Received 17.2% 8.5% 9.6% 19.2% 8 % Won to Date of Submitted 66.8% 2.5% 5.9% 8.2% 9 % Won to Date of Received 1.5% 2.0% 4.8% 4.3% ------------------------------------------------------------------------------------------------------------ Another potential problem here is that my enduser has requested a custom-type chart called "Lines on Two Axes" and I am unfamiliar with how to code for this type of chart I am able to create this chart manually. The "record" code in concert with my lack of experience with this type of chart is a problem. Again, thanks- JR --------------------------------------------------------------------------------------------------------------------------- Andy Pope wrote: Hi, Does the range defined by topleft and bottomright have enough columns to have series placed on the secondary axis? I would imagine trying to set the properties of the secondary axis that does not exist would cause problems. Cheers Andy Jimmy wrote: Hi, I am creating a chart in Excel. At lines 15-17 (see below) I get "Method 'Axes' of Object 'Chart_Chart' failed." Any help in getting this chart to create would be greatly appreciated. Thanks, Jimmy 2 Charts.Add 3 ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ 4 "Lines on 2 Axes" 5 ActiveChart.SetSourceData Source:=Sheets("Chart").Range(topLeft & ":" & bottomRight), PlotBy:= _ 6 xlRows 7 ActiveChart.Location Whe=xlLocationAsObject, Name:="Chart" 8 With ActiveChart 9 .HasTitle = True 10 .ChartTitle.Characters.Text = "Bid/Quote Success Rate" 11 .Axes(xlCategory, xlPrimary).HasTitle = False 12 .Axes(xlValue, xlPrimary).HasTitle = True 13 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ 14 "# of Bids/Quotes Received" 15 ' .Axes(xlCategory, xlSecondary).HasTitle = False 16 ' .Axes(xlValue, xlSecondary).HasTitle = True 17 ' .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = _ 18 "% Submitted of Received or % Won to Date of Submitted/Received" 19 End With -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
Chart VBA
Thanks, Andy!
Andy Pope wrote: Hi, This code will produce your chart. Don't worry about using the built-in custom type. You can create the same thing yourself. Sub Jimmy() topleft = "E5" bottomright = "I9" Charts.Add ActiveChart.SetSourceData Source:=Sheets("Chart").Range( _ topleft & ":" & bottomright), PlotBy:=xlRows ActiveChart.Location Whe=xlLocationAsObject, Name:="Chart" With ActiveChart .ChartType = xlLine .HasTitle = True .ChartTitle.Characters.Text = "Bid/Quote Success Rate" ' move to secondary axis For lngseries = 2 To .SeriesCollection.Count .SeriesCollection(lngseries).AxisGroup = 2 Next .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ "# of Bids/Quotes Received" .Axes(xlCategory, xlSecondary).HasTitle = False .Axes(xlValue, xlSecondary).HasTitle = True .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = _ "% Submitted of Received or % Won to Date of Submitted/Received" End With End Sub Cheers Andy Jimmy wrote: Jimmy wrote: Thanks for getting back to me, Andy. ------------------------------------------------------------------------------------------------------------- XL02 ------------------------------------------------------------- In this case topright = E5 and bottomleft = Q9 The goal is for Row6 to be plotted on the primary axis and for Rows 7-9 to be plotted on the secondary access. Here is a smattering of the chart data I use: E F G H I 5 Mar-00 Apr-04 Jun-04 Apr-05 6 # of Bids/Quotes Received 768 117 1,050 172 7 % Submitted of Received 17.2% 8.5% 9.6% 19.2% 8 % Won to Date of Submitted 66.8% 2.5% 5.9% 8.2% 9 % Won to Date of Received 1.5% 2.0% 4.8% 4.3% ------------------------------------------------------------------------------------------------------------ Another potential problem here is that my enduser has requested a custom-type chart called "Lines on Two Axes" and I am unfamiliar with how to code for this type of chart I am able to create this chart manually. The "record" code in concert with my lack of experience with this type of chart is a problem. Again, thanks- JR --------------------------------------------------------------------------------------------------------------------------- Andy Pope wrote: Hi, Does the range defined by topleft and bottomright have enough columns to have series placed on the secondary axis? I would imagine trying to set the properties of the secondary axis that does not exist would cause problems. Cheers Andy Jimmy wrote: Hi, I am creating a chart in Excel. At lines 15-17 (see below) I get "Method 'Axes' of Object 'Chart_Chart' failed." Any help in getting this chart to create would be greatly appreciated. Thanks, Jimmy 2 Charts.Add 3 ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:= _ 4 "Lines on 2 Axes" 5 ActiveChart.SetSourceData Source:=Sheets("Chart").Range(topLeft & ":" & bottomRight), PlotBy:= _ 6 xlRows 7 ActiveChart.Location Whe=xlLocationAsObject, Name:="Chart" 8 With ActiveChart 9 .HasTitle = True 10 .ChartTitle.Characters.Text = "Bid/Quote Success Rate" 11 .Axes(xlCategory, xlPrimary).HasTitle = False 12 .Axes(xlValue, xlPrimary).HasTitle = True 13 .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = _ 14 "# of Bids/Quotes Received" 15 ' .Axes(xlCategory, xlSecondary).HasTitle = False 16 ' .Axes(xlValue, xlSecondary).HasTitle = True 17 ' .Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = _ 18 "% Submitted of Received or % Won to Date of Submitted/Received" 19 End With -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info |
| All times are GMT +1. The time now is 12:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com