Chart Legend randomly displayed in exported PDF file
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:
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
