|
Another VBA Chart problem Excel 97
Hi I have used macro recorder to create a chart on a sheet which is run
under sheet activate. The problem is the code referances 'Chart 1.... etc' obviously each chart created on one of 37 sheets a new Chart number is created so the code wont run. Prob 1: Can "Chart 17" in the code be replaced with say activechart? Prob 2: I need the graph to be placed in cells C2:AF10 is there a way I can specify the exact size and position of the chart within the code? Code Below Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "='Mon 2nd-w1'!R43C3:R43C32" ActiveChart.SeriesCollection(1).Values = "='Mon 2nd-w1'!R44C3:R44C32" ActiveChart.SeriesCollection(2).Values = "='Mon 2nd-w1'!R16C3:R16C32" ActiveChart.SeriesCollection(3).Values = "='Mon 2nd-w1'!R51C3:R51C32" ActiveChart.Location Whe=xlLocationAsObject, Name:="Mon 2nd-w1" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveSheet.Shapes("Chart 17").IncrementLeft -741# ActiveSheet.Shapes("Chart 17").IncrementTop -546# ActiveWindow.Visible = False ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.Axes(xlCategory).Select ActiveWindow.Visible = False Windows("Monthly Wage Tool - Blank 2006 v1_1.xls").Activate Range("C1").Select ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 17").ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes("Chart 17").ScaleHeight 0.91, msoFalse, msoScaleFromBottomRight Many Thanks as always for your replies and most of all to Tom who has saved me so much time Cheers :-) |
Another VBA Chart problem Excel 97
Prob 1
ActiveChart.Parent.ShapeRange.ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft Prob 2 Set r = Range("C2:F10") With ActiveChart.Parent .Left = r.Left .Top = r.Top .Width = r.Width .Height = r.Height End With did you really mean sized to C2:AF10 ! Regards, Peter T "Karoo News" wrote in message ... Hi I have used macro recorder to create a chart on a sheet which is run under sheet activate. The problem is the code referances 'Chart 1.... etc' obviously each chart created on one of 37 sheets a new Chart number is created so the code wont run. Prob 1: Can "Chart 17" in the code be replaced with say activechart? Prob 2: I need the graph to be placed in cells C2:AF10 is there a way I can specify the exact size and position of the chart within the code? Code Below Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "='Mon 2nd-w1'!R43C3:R43C32" ActiveChart.SeriesCollection(1).Values = "='Mon 2nd-w1'!R44C3:R44C32" ActiveChart.SeriesCollection(2).Values = "='Mon 2nd-w1'!R16C3:R16C32" ActiveChart.SeriesCollection(3).Values = "='Mon 2nd-w1'!R51C3:R51C32" ActiveChart.Location Whe=xlLocationAsObject, Name:="Mon 2nd-w1" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveSheet.Shapes("Chart 17").IncrementLeft -741# ActiveSheet.Shapes("Chart 17").IncrementTop -546# ActiveWindow.Visible = False ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.Axes(xlCategory).Select ActiveWindow.Visible = False Windows("Monthly Wage Tool - Blank 2006 v1_1.xls").Activate Range("C1").Select ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 17").ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes("Chart 17").ScaleHeight 0.91, msoFalse, msoScaleFromBottomRight Many Thanks as always for your replies and most of all to Tom who has saved me so much time Cheers :-) |
Another VBA Chart problem Excel 97
Assuming you want to add a new "sized" chart - do the sizing first and no
need to select anything Set r = Range("C2:F10") With r Set chtobj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height) End With With chtobj.Chart 'code End With "Peter T" <peter_t@discussions wrote in message ... Prob 1 ActiveChart.Parent.ShapeRange.ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft Prob 2 Set r = Range("C2:F10") With ActiveChart.Parent .Left = r.Left .Top = r.Top .Width = r.Width .Height = r.Height End With did you really mean sized to C2:AF10 ! Regards, Peter T "Karoo News" wrote in message ... Hi I have used macro recorder to create a chart on a sheet which is run under sheet activate. The problem is the code referances 'Chart 1.... etc' obviously each chart created on one of 37 sheets a new Chart number is created so the code wont run. Prob 1: Can "Chart 17" in the code be replaced with say activechart? Prob 2: I need the graph to be placed in cells C2:AF10 is there a way I can specify the exact size and position of the chart within the code? Code Below Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "='Mon 2nd-w1'!R43C3:R43C32" ActiveChart.SeriesCollection(1).Values = "='Mon 2nd-w1'!R44C3:R44C32" ActiveChart.SeriesCollection(2).Values = "='Mon 2nd-w1'!R16C3:R16C32" ActiveChart.SeriesCollection(3).Values = "='Mon 2nd-w1'!R51C3:R51C32" ActiveChart.Location Whe=xlLocationAsObject, Name:="Mon 2nd-w1" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveSheet.Shapes("Chart 17").IncrementLeft -741# ActiveSheet.Shapes("Chart 17").IncrementTop -546# ActiveWindow.Visible = False ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.Axes(xlCategory).Select ActiveWindow.Visible = False Windows("Monthly Wage Tool - Blank 2006 v1_1.xls").Activate Range("C1").Select ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 17").ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes("Chart 17").ScaleHeight 0.91, msoFalse, msoScaleFromBottomRight Many Thanks as always for your replies and most of all to Tom who has saved me so much time Cheers :-) |
Another VBA Chart problem Excel 97
Yes did mean size the chart to cover those cells!
Many thanks for your reply your a star :-) "Peter T" <peter_t@discussions wrote in message ... Assuming you want to add a new "sized" chart - do the sizing first and no need to select anything Set r = Range("C2:F10") With r Set chtobj = ActiveSheet.ChartObjects.Add(.Left, .Top, .Width, .Height) End With With chtobj.Chart 'code End With "Peter T" <peter_t@discussions wrote in message ... Prob 1 ActiveChart.Parent.ShapeRange.ScaleWidth 0.7, msoFalse, msoScaleFromTopLeft Prob 2 Set r = Range("C2:F10") With ActiveChart.Parent .Left = r.Left .Top = r.Top .Width = r.Width .Height = r.Height End With did you really mean sized to C2:AF10 ! Regards, Peter T "Karoo News" wrote in message ... Hi I have used macro recorder to create a chart on a sheet which is run under sheet activate. The problem is the code referances 'Chart 1.... etc' obviously each chart created on one of 37 sheets a new Chart number is created so the code wont run. Prob 1: Can "Chart 17" in the code be replaced with say activechart? Prob 2: I need the graph to be placed in cells C2:AF10 is there a way I can specify the exact size and position of the chart within the code? Code Below Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Mon 2nd-w1").Range("A1") ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection.NewSeries ActiveChart.SeriesCollection(1).XValues = "='Mon 2nd-w1'!R43C3:R43C32" ActiveChart.SeriesCollection(1).Values = "='Mon 2nd-w1'!R44C3:R44C32" ActiveChart.SeriesCollection(2).Values = "='Mon 2nd-w1'!R16C3:R16C32" ActiveChart.SeriesCollection(3).Values = "='Mon 2nd-w1'!R51C3:R51C32" ActiveChart.Location Whe=xlLocationAsObject, Name:="Mon 2nd-w1" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With ActiveSheet.Shapes("Chart 17").IncrementLeft -741# ActiveSheet.Shapes("Chart 17").IncrementTop -546# ActiveWindow.Visible = False ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.Axes(xlCategory).Select ActiveWindow.Visible = False Windows("Monthly Wage Tool - Blank 2006 v1_1.xls").Activate Range("C1").Select ActiveSheet.ChartObjects("Chart 17").Activate ActiveChart.ChartArea.Select ActiveSheet.Shapes("Chart 17").ScaleHeight 0.7, msoFalse, msoScaleFromTopLeft ActiveSheet.Shapes("Chart 17").ScaleHeight 0.91, msoFalse, msoScaleFromBottomRight Many Thanks as always for your replies and most of all to Tom who has saved me so much time Cheers :-) |
| All times are GMT +1. The time now is 12:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com