Consulting

Results 1 to 2 of 2

Thread: Chart Legend randomly displayed in exported PDF file

  1. #1

    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:

    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

  2. #2
    I solved the problem with including these two rows:

        ActiveChart.SetElement (msoElementLegendRight)
        ActiveChart.SetElement (msoElementLegendBottom)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •