[VBA] Edit and Move my diagram

[o0omax]

hello I have a Problem with my Sub MoveAndEdit.

I try to call it in my previous Sub but somehow it doesnt work. Does somebody has an solution?

My goal is to move and edit all the Diagrams I creted in the first Sub.



Sub DiagrammErstellen()

Dim DL As Integer '#Zeilen
Dim i As Integer ' Zeilen Laufindex

Dim sheet As Worksheet

Set MyWorksheet = ThisWorkbook.Sheets("KPI")
Application.CutCopyMode = False

DL = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To DL

With MyWorksheet.Shapes.AddChart2(216, xlBarClustered).Chart

.ChartArea.ClearContents
.SeriesCollection.NewSeries

With .FullSeriesCollection(1)

.Name = "=Pivot! " & Cells(i, 1).Address
.Values = "=Pivot!" & Range(Cells(i, 2), Cells(i, 3)).Address
End With

Call MoveAndEdit

'.HasLegend = False
End With
Next i

End Sub

Sub MoveAndEdit()

With ActiveChart

.Width = 10
.Height = 10
.Top = Cells(i, 2).Top
.Left = Cells(i, 2).Top

End With

End Sub

[p45cal]

Not verified:
Change:
Sub MoveAndEdit()
to:
Sub MoveAndEdit(i)

Change:
Call MoveAndEdit
to:
MoveAndEdit i

ps. are you sure
.Left = Cells(i, 2).Top
shouldn't be different? Perhaps:
.Left = Cells(i, 2).Left

[o0omax]

Thanks! I solved it by not calling a sub.

The Code grew and looks like this, feedback appreciated!

Thanks to you the positioning of the diagram works. I forgot to write "Left"


Sub DiagrammErstellen()

Dim DL As Integer '#Zeilen
Dim i As Integer ' Zeilen Laufindex

Dim sheet As Worksheet

Set MyWorksheet = ThisWorkbook.Sheets("KPI")
Application.CutCopyMode = False

DL = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To DL

If Cells(14, i) = FALSCH Then

With MyWorksheet.Shapes.AddChart2(216, xlBarClustered)


'--------Größe und Position------
.Width = 200
.Height = 50
.Top = Cells(2, i).Top
.Left = Cells(2, i).Left

'----------Layout--------------
.Fill.Visible = msoFalse
.Line.Visible = msoFalse

With .Chart
.ChartArea.ClearContents
.SeriesCollection.NewSeries

With .FullSeriesCollection(1)

.Name = "=Pivot! " & Cells(i, 1).Address
.Values = "=Pivot!" & Range(Cells(i, 2), Cells(i, 3)).Address
.XValues = "=Pivot!" & Range(Cells(1, 2), Cells(1, 3)).Address

End With

.SetElement (msoElementPrimaryValueGridLinesNone)
.SetElement (msoElementDataLabelOutSideEnd)
.ChartGroups(1).GapWidth = 0
.ChartTitle.Delete
.Axes(xlValue).Delete
.Axes(xlCategory).Delete

''---------Layout2-------- Balken 1 bei selection format line braucht man nicht alles
.FullSeriesCollection(1).Points(2).Select

With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(205, 219, 229)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.RGB = RGB(0, 0, 0)
.ForeColor.Brightness = 0
End With

ActiveChart.FullSeriesCollection(1).Points(1).Select

With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(180, 202, 216)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorText1
.ForeColor.TintAndShade = 0
.ForeColor.RGB = RGB(0, 0, 0)
.ForeColor.Brightness = 0
.Transparency = 0

'.HasLegend = False
End With

End With

End With

End If
Next i

End Sub