none
Legend duplicates each time Chart creation macro is run RRS feed

  • Question

  • I have the following macro that I use to create a chart sheet with a copy of a chart (from a sheet with many small charts). this does two things: it creates a chart sheet if one does not exist and serves to enlarge the small chart for better viewing.  When the macro runs a second (and subsequent) time the chart remains the same if it exists but the legend is duplicated. This is the only chart that requires a legend so went un-noticed on the other sheets. I've copied a copy of the fourth copy version below. As you can see I get four legends for the same sheet (and it goes on until the chart area is full).

    Hopefully someone can help.

    The Code:

    Sub FTETypeChart25()
    '
    ' Create or enlarge charts to separate sheet
    '

    '
        Dim strName As String
         Dim wsh As Worksheet
         Dim chtSht As Chart
        
         strName = "Chart FTE Type"
         Debug.Print strName
         Set wsh = Sheets("FTE Charts")
        
         On Error Resume Next
         'Attempt to assign the chart sheet to a chart object variable
         'Code will error if sheet does not exist and hence the On Error Resume Next
         Set chtSht = Charts(strName)
         On Error GoTo 0
        
         If chtSht Is Nothing Then    'If chart sheet name not found
             Set chtSht = ActiveWorkbook.Charts.Add(before:=Worksheets("FTE Charts")) 'Sheets(Sheets.Count))
             chtSht.Name = strName
         End If
        
         With wsh
             .ChartObjects("Chart 25").Copy
             'Following is a Generic line to replace above line if only one chart on the sheet
             '.ChartObjects(1).Copy
             With chtSht
                 .Activate
                 .Paste
                 '.ChartType = xlLine 'use default as this is not the same as the original
                 .Tab.Color = vbYellow
                 .SetElement (msoElementLegendBottom) 'tried to stop dulpication of the legend each time the macro runs
             End With
         End With
    End Sub

    The Chart:

    Tuesday, October 30, 2018 1:02 AM

All replies

  • Hi Jim,

    >>When the macro runs a second (and subsequent) time the chart remains the same if it exists but the legend is duplicated.

    Do you want run the legend only once?

    According to your code, When you run the macro, the legend will run, regardless of the existence of the chart. I suggest that you can add a condition to determine.

    Or you could refer to the following code:

     If chtSht Is Nothing Then    'If chart sheet name not found
             Set chtSht = ActiveWorkbook.Charts.Add(before:=Worksheets("FTE Charts")) 'Sheets(Sheets.Count))
             chtSht.Name = strName  
         
         With wsh
             .ChartObjects("Chart 25").Copy
             'Following is a Generic line to replace above line if only one chart on the sheet
             '.ChartObjects(1).Copy
             With chtSht
                 .Activate
                 .Paste
                 '.ChartType = xlLine 'use default as this is not the same as the original
                 .Tab.Color = vbYellow
                 .SetElement (msoElementLegendBottom) 'tried to stop dulpication of the legend each time the macro runs
             End With
         End With
    End If

    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.


    • Edited by Lina-MSFT Tuesday, October 30, 2018 6:23 AM
    Tuesday, October 30, 2018 3:14 AM
  • Thanks Lina,

    Unfortunately, I get a chart sheet with the Title and no chart!

    I don't want to have the legend more than once, but I can't work out why only the legend is duplicated when the macro is re-run when the sheet exists. I had expected the macro to stop if the sheet name was found to exist. 

    Cheers

    Jim

    Tuesday, October 30, 2018 4:37 AM
  • Hi Jim,

    >>I had expected the macro to stop if the sheet name was found to exist. 

    Please refer to the following code:

     If chtSht Is Nothing Then    'If chart sheet name not found
             Set chtSht = ActiveWorkbook.Charts.Add(before:=Worksheets("FTE Charts")) 'Sheets(Sheets.Count))
             chtSht.Name = strName

    ElseIf chtSht is Not Nothing Then Exit Sub

     End If


    Hopefully it helps you.

    Best Regards,

    Lina


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, October 30, 2018 6:59 AM
  • Thanks Lina,

    This was the error result:

    It appears the image didn't copy: The error response was "Compile error": Invalid use of object: ElseIf chtSht Is Not 'Nothing' Then

    Hopefully this explains it.

    Cheers

    Jim


    • Edited by Jim from Oz Wednesday, October 31, 2018 1:38 AM add error message detail
    Tuesday, October 30, 2018 10:51 PM