farmerscott
Well-known Member
- Joined
- Jan 26, 2013
- Messages
- 819
- Office Version
- 365
- Platform
- Windows
HI Everybody,
I am just starting to learn some VBA associated with charts and am finding it difficult with the syntax.
I have the following (piecemeal from the web) code which works, however I would like to know the difference between the two With/With End structures and why the 2nd code in blue does not work when I paste it into the first With/With End statement.
Any good tips or websites on charting would be highly appreciated. I am struggling with-
1. Positioning of the chart relative to cell references (ie Range("J5")).
2. The relationship between the parent of the chart(???) and the chart itself (chartobjects/chartobject/shape???)
thx
FarmerScott
I am just starting to learn some VBA associated with charts and am finding it difficult with the syntax.
I have the following (piecemeal from the web) code which works, however I would like to know the difference between the two With/With End structures and why the 2nd code in blue does not work when I paste it into the first With/With End statement.
Code:
Sub Create_Chart()
Dim rng As Range
Dim cht As ChartObject
Dim ChrtSrs1 As Series
'Your data range for the chart
Set rng = Worksheets("Decade").Range("E2:F15")
'Create a chart
Set cht = Worksheets("Decade").ChartObjects.Add(Left:=100, Width:=400, Top:=100, Height:=250)
With cht
.Top = Range("J2").Top
.Left = Range("J2").Left
.Width = Range("J2:S2").Width
.Height = Range("J2:J23").Height
[COLOR=#0000ff].Chart.HasTitle = True 'this works
.Chart.ChartTitle.Text = Worksheets("Decade").Range("F1").Value 'this works[/COLOR]
End With
'Determine the chart type
With cht.Chart
[COLOR=#0000ff]'why does this not work when pasted in the previous with/With End statement[/COLOR]
[COLOR=#0000ff].ChartType = xlColumnClustered
.HasLegend = False[/COLOR]
End With
'Determine the chart series and colour
Set ChrtSrs1 = cht.Chart.SeriesCollection.NewSeries
With ChrtSrs1
.Values = "='Decade'!$F$2:$F$15"
.XValues = "='Decade'!$E$2:$E$15"
.Interior.Color = vbBlue
End With
End Sub
Any good tips or websites on charting would be highly appreciated. I am struggling with-
1. Positioning of the chart relative to cell references (ie Range("J5")).
2. The relationship between the parent of the chart(???) and the chart itself (chartobjects/chartobject/shape???)
thx
FarmerScott
Last edited: