Hello guys,

im new here, and I would like to start directly with my first question/problem.

Im going to write a vba tool. In this tool I create a chart and export this chart as a PDF file.
For the creation of the chart I used the Macro Recorder (Excel 2013). During the recording I use a legend activated recommended chart. But when I start the macro sometimes the chart does not have the legend. Sometimes it has.

Does someone know how we can solve this problem?

Here is my code:

Sub Macro2(sheet As String, column As Integer, exist As Integer)    
    
    If exist <> 1 Then
    
        Sheets(sheet).Select
        Sheets.Add After:=ActiveSheet
        Sheets(ActiveSheet.name).Select
    '    Sheets(ActiveSheet.name).name = "Sheet2"
        Sheets(ActiveSheet.name).Select
        On Error Resume Next
        Sheets(ActiveSheet.name).name = sheet & " - Chart"
        On Error Resume Next
    End If
    
    Sheets(sheet).Select
'    Range("A1:G109").Select
    ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    ActiveChart.SetSourceData Source:=Sheets(sheet).Range(Cells(1, 1), Cells(109, column))
    ActiveChart.Parent.Cut
    Sheets(sheet & " - Chart").Select
    
    'Clear all contents in sheet
    ActiveSheet.Cells.Clear
    ActiveSheet.ChartObjects.Delete
    
    ActiveSheet.Paste
    ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementLeft 48
    ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementTop 15
    ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    ActiveSheet.Shapes(ActiveChart.Parent.name).ScaleWidth 3.0645833333, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes(ActiveChart.Parent.name).ScaleHeight 1.9513888889, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementLeft -30
    ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementLeft 0.75
    ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementTop -8.25
    ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    ActiveChart.ClearToMatchStyle
    ActiveChart.ChartStyle = 304
    Application.CommandBars("Format Object").Visible = False
    ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = sheet & " - Workload"
    Selection.Format.TextFrame2.TextRange.Characters.Text = _
        sheet & " - Workload"
    On Error Resume Next
    With Selection.Format.TextFrame2.TextRange.Characters(1, 25).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    On Error Resume Next
    With Selection.Format.TextFrame2.TextRange.Characters(1, 25).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Shadow.Type = msoShadow22
        .Shadow.Visible = msoTrue
        .Shadow.Style = msoShadowStyleOuterShadow
        .Shadow.Blur = 4
        .Shadow.OffsetX = 1.8369701987E-16
        .Shadow.OffsetY = 3
        .Shadow.RotateWithShape = msoFalse
        .Shadow.ForeColor.RGB = RGB(0, 0, 0)
        .Shadow.Transparency = 0.599999994
        .Shadow.Size = 100
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(242, 242, 242)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 16
        .Italic = msoFalse
        .Kerning = 12
        .name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 1
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.PlotArea.Select
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.ChartArea.Select
    ActiveChart.FullSeriesCollection(1).ChartType = xlLine
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
'    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    ActiveChart.SetElement msoElementPrimaryValueAxisTitleBelowAxis
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "hours"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "hours"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 5).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .Caps = msoAllCaps
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(217, 217, 217)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 9
        .Italic = msoFalse
        .Kerning = 12
        .name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
    End With
    ActiveChart.Axes(xlCategory).AxisTitle.Select
    ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "months"
    Selection.Format.TextFrame2.TextRange.Characters.Text = "months"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 6).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font
        .BaselineOffset = 0
        .Bold = msoTrue
        .Caps = msoAllCaps
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(217, 217, 217)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 9
        .Italic = msoFalse
        .Kerning = 12
        .name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Strike = msoNoStrike
'        .SetElement (msoElementLegendRight)
         
    End With


    
    With ActiveChart.Parent
    .Top = Range("A1").Top
    .Left = Range("A1").Left
    'include these lines to make it fit exactly on J22:
    .Height = Range("A1:A30").Height
    .Width = Range("A1:X1").Width
End With
    
   
    
'    ActiveChart.ChartArea.Select
'    ActiveChart.SetElement (msoElementLegendRight)
'    Range("X19").Select


End Sub