Chart Legende wird manchmal nicht angezeigt - Makro Rekorder

  • Excel

Es gibt 1 Antwort in diesem Thema. Der letzte Beitrag () ist von vbneuling60.

    Chart Legende wird manchmal nicht angezeigt - Makro Rekorder

    Hallo zusammen,

    ich bin gerade dabei ein VBA Tool zu schreiben. In meinem Problem geht es darum, dass ich bei der Erstellung eines Chart die Legende manchmal nicht angezeigt bekomme. Fuer die Erstellung des Charts habe ich den Makro Rekorder (2013) verwendet. Das klappt soweit wunderbar. Ich verwende ein empfohlenes Chart mit Legende. Aber wenn ich den Makro danach ausfuehre kommt es manchmal vor, dass die Legende einfach nicht angezeigt wird. Manchmal wird sie angezeigt, manchmal nicht.

    Kennt jemand dieses Problem?

    Hier ist mein Code fur die Erstellung des Charts:

    VB.NET-Quellcode

    1. Sub Macro2(sheet As String, column As Integer, exist As Integer)
    3. If exist <> 1 Then
    5. Sheets(sheet).Select
    6. Sheets.Add After:=ActiveSheet
    7. Sheets(ActiveSheet.name).Select
    8. ' Sheets(ActiveSheet.name).name = "Sheet2"
    9. Sheets(ActiveSheet.name).Select
    10. On Error Resume Next
    11. Sheets(ActiveSheet.name).name = sheet & " - Chart"
    12. On Error Resume Next
    13. End If
    15. Sheets(sheet).Select
    16. ' Range("A1:G109").Select
    17. ActiveSheet.Shapes.AddChart2(297, xlColumnStacked).Select
    18. ActiveChart.SetSourceData Source:=Sheets(sheet).Range(Cells(1, 1), Cells(109, column))
    19. ActiveChart.Parent.Cut
    20. Sheets(sheet & " - Chart").Select
    22. 'Clear all contents in sheet
    23. ActiveSheet.Cells.Clear
    24. ActiveSheet.ChartObjects.Delete
    26. ActiveSheet.Paste
    27. ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    28. ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementLeft 48
    29. ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementTop 15
    30. ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    31. ActiveSheet.Shapes(ActiveChart.Parent.name).ScaleWidth 3.0645833333, msoFalse, _
    32. msoScaleFromTopLeft
    33. ActiveSheet.Shapes(ActiveChart.Parent.name).ScaleHeight 1.9513888889, msoFalse, _
    34. msoScaleFromTopLeft
    35. ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    36. ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementLeft -30
    37. ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    38. ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementLeft 0.75
    39. ActiveSheet.Shapes(ActiveChart.Parent.name).IncrementTop -8.25
    40. ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    41. ActiveChart.ClearToMatchStyle
    42. ActiveChart.ChartStyle = 304
    43. Application.CommandBars("Format Object").Visible = False
    44. ActiveSheet.ChartObjects(ActiveChart.Parent.name).Activate
    45. ActiveChart.ChartTitle.Select
    46. ActiveChart.ChartTitle.Text = sheet & " - Workload"
    47. Selection.Format.TextFrame2.TextRange.Characters.Text = _
    48. sheet & " - Workload"
    49. On Error Resume Next
    50. With Selection.Format.TextFrame2.TextRange.Characters(1, 25).ParagraphFormat
    51. .TextDirection = msoTextDirectionLeftToRight
    52. .Alignment = msoAlignCenter
    53. End With
    54. On Error Resume Next
    55. With Selection.Format.TextFrame2.TextRange.Characters(1, 25).Font
    56. .BaselineOffset = 0
    57. .Bold = msoTrue
    58. .NameComplexScript = "+mn-cs"
    59. .NameFarEast = "+mn-ea"
    60. .Shadow.Type = msoShadow22
    61. .Shadow.Visible = msoTrue
    62. .Shadow.Style = msoShadowStyleOuterShadow
    63. .Shadow.Blur = 4
    64. .Shadow.OffsetX = 1.8369701987E-16
    65. .Shadow.OffsetY = 3
    66. .Shadow.RotateWithShape = msoFalse
    67. .Shadow.ForeColor.RGB = RGB(0, 0, 0)
    68. .Shadow.Transparency = 0.599999994
    69. .Shadow.Size = 100
    70. .Fill.Visible = msoTrue
    71. .Fill.ForeColor.RGB = RGB(242, 242, 242)
    72. .Fill.Transparency = 0
    73. .Fill.Solid
    74. .Size = 16
    75. .Italic = msoFalse
    76. .Kerning = 12
    77. .name = "+mn-lt"
    78. .UnderlineStyle = msoNoUnderline
    79. .Spacing = 1
    80. .Strike = msoNoStrike
    81. End With
    82. ActiveChart.ChartArea.Select
    83. Application.CommandBars("Format Object").Visible = False
    84. ActiveChart.PlotArea.Select
    85. ActiveChart.FullSeriesCollection(1).Select
    86. ActiveChart.ChartArea.Select
    87. ActiveChart.FullSeriesCollection(1).ChartType = xlLine
    88. Application.CommandBars("Format Object").Visible = False
    89. ActiveChart.SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
    90. ' ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    91. ActiveChart.SetElement msoElementPrimaryValueAxisTitleBelowAxis
    92. ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = "hours"
    93. Selection.Format.TextFrame2.TextRange.Characters.Text = "hours"
    94. With Selection.Format.TextFrame2.TextRange.Characters(1, 5).ParagraphFormat
    95. .TextDirection = msoTextDirectionLeftToRight
    96. .Alignment = msoAlignCenter
    97. End With
    98. With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
    99. .BaselineOffset = 0
    100. .Bold = msoTrue
    101. .Caps = msoAllCaps
    102. .NameComplexScript = "+mn-cs"
    103. .NameFarEast = "+mn-ea"
    104. .Fill.Visible = msoTrue
    105. .Fill.ForeColor.RGB = RGB(217, 217, 217)
    106. .Fill.Transparency = 0
    107. .Fill.Solid
    108. .Size = 9
    109. .Italic = msoFalse
    110. .Kerning = 12
    111. .name = "+mn-lt"
    112. .UnderlineStyle = msoNoUnderline
    113. .Strike = msoNoStrike
    114. End With
    115. ActiveChart.Axes(xlCategory).AxisTitle.Select
    116. ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Text = "months"
    117. Selection.Format.TextFrame2.TextRange.Characters.Text = "months"
    118. With Selection.Format.TextFrame2.TextRange.Characters(1, 6).ParagraphFormat
    119. .TextDirection = msoTextDirectionLeftToRight
    120. .Alignment = msoAlignCenter
    121. End With
    122. With Selection.Format.TextFrame2.TextRange.Characters(1, 6).Font
    123. .BaselineOffset = 0
    124. .Bold = msoTrue
    125. .Caps = msoAllCaps
    126. .NameComplexScript = "+mn-cs"
    127. .NameFarEast = "+mn-ea"
    128. .Fill.Visible = msoTrue
    129. .Fill.ForeColor.RGB = RGB(217, 217, 217)
    130. .Fill.Transparency = 0
    131. .Fill.Solid
    132. .Size = 9
    133. .Italic = msoFalse
    134. .Kerning = 12
    135. .name = "+mn-lt"
    136. .UnderlineStyle = msoNoUnderline
    137. .Strike = msoNoStrike
    138. ' .SetElement (msoElementLegendRight)
    140. End With
    144. With ActiveChart.Parent
    145. .Top = Range("A1").Top
    146. .Left = Range("A1").Left
    147. 'include these lines to make it fit exactly on J22:
    148. .Height = Range("A1:A30").Height
    149. .Width = Range("A1:X1").Width
    150. End With
    154. ' ActiveChart.ChartArea.Select
    155. ' ActiveChart.SetElement (msoElementLegendRight)
    156. ' Range("X19").Select
    159. End Sub



    Mit besten Gruessen,

Facebook