Bearded Cyclops
New Member
- Joined
- Oct 27, 2002
- Messages
- 1
HI,
I'm working on a subroutine of larger piece of code that creates graphs for several diferent offices in my company. The following code is the section i'm struggling with.
Sub create_charts() 'create charts from the pivot tables
For x = 1 To noo ' number of offices
cur_office = Worksheets("control").Range("A" & (4 + x))
Worksheets(cur_office).Select
Range("A2").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(cur_office).Range("F11")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=" & cur_office & "!R4C3:R" & (3 + month) & "C3"
ActiveChart.SeriesCollection(1).Values = "=" & cur_office & "!R4C2:R" & (3 + month) & "C2"
ActiveChart.SeriesCollection(1).Name = "=""Sales NN"""
ActiveChart.SeriesCollection(2).XValues = "=" & cur_office & "!R4C3:R" & (3 + month) & "C3"
ActiveChart.SeriesCollection(2).Values = "=" & cur_office & "!R" & (4 + month) & "C2:R" & (3 + month + month) & "C2"
ActiveChart.SeriesCollection(2).Name = "=""Sales Target NN"""
ActiveChart.SeriesCollection(3).XValues = "=" & cur_office & "!R4C3:R" & (3 + month) & "C3"
ActiveChart.SeriesCollection(3).Values = "=" & cur_office & "!R" & (4 + month + month) & "C2:R" & (3 + month + month + month) & "C2"
ActiveChart.SeriesCollection(3).Name = "=""Orders Booked NN"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=cur_office
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Characters.Text = "Change This Title in CODE"
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With
ActiveSheet.DrawingObjects.Placement = xlMove
ActiveSheet.DrawingObjects.PrintObject = True
**********************************
NEED HELP HERE
**********************************
With ActiveSheet.Charts(cur_office).Shape '*****this is wrong
.IncrementLeft -183#
.IncrementTop -85
.ScaleWidth 1.78, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.88, msoFalse, msoScaleFromTopLeft
End With
Next x
End Sub
I've noted the section I need help with. How do I select the shapes in the chart so I can move them? Is there a way to rename a shape?
Hope this is not tomuch to ask. Thanks!
I'm working on a subroutine of larger piece of code that creates graphs for several diferent offices in my company. The following code is the section i'm struggling with.
Sub create_charts() 'create charts from the pivot tables
For x = 1 To noo ' number of offices
cur_office = Worksheets("control").Range("A" & (4 + x))
Worksheets(cur_office).Select
Range("A2").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets(cur_office).Range("F11")
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=" & cur_office & "!R4C3:R" & (3 + month) & "C3"
ActiveChart.SeriesCollection(1).Values = "=" & cur_office & "!R4C2:R" & (3 + month) & "C2"
ActiveChart.SeriesCollection(1).Name = "=""Sales NN"""
ActiveChart.SeriesCollection(2).XValues = "=" & cur_office & "!R4C3:R" & (3 + month) & "C3"
ActiveChart.SeriesCollection(2).Values = "=" & cur_office & "!R" & (4 + month) & "C2:R" & (3 + month + month) & "C2"
ActiveChart.SeriesCollection(2).Name = "=""Sales Target NN"""
ActiveChart.SeriesCollection(3).XValues = "=" & cur_office & "!R4C3:R" & (3 + month) & "C3"
ActiveChart.SeriesCollection(3).Values = "=" & cur_office & "!R" & (4 + month + month) & "C2:R" & (3 + month + month + month) & "C2"
ActiveChart.SeriesCollection(3).Name = "=""Orders Booked NN"""
ActiveChart.Location Where:=xlLocationAsObject, Name:=cur_office
ActiveChart.HasTitle = True
ActiveChart.ChartTitle.Characters.Text = "Change This Title in CODE"
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = True
ActiveChart.DataTable.ShowLegendKey = True
ActiveChart.ChartArea.Select
Selection.AutoScaleFont = False
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 12
End With
ActiveSheet.DrawingObjects.Placement = xlMove
ActiveSheet.DrawingObjects.PrintObject = True
**********************************
NEED HELP HERE
**********************************
With ActiveSheet.Charts(cur_office).Shape '*****this is wrong
.IncrementLeft -183#
.IncrementTop -85
.ScaleWidth 1.78, msoFalse, msoScaleFromTopLeft
.ScaleHeight 1.88, msoFalse, msoScaleFromTopLeft
End With
Next x
End Sub
I've noted the section I need help with. How do I select the shapes in the chart so I can move them? Is there a way to rename a shape?
Hope this is not tomuch to ask. Thanks!