none
Subroutine to refer to existing chart

    Question

  • Hey! Does anybody know a VBA code to refer/identify the chart which is already created?

    I have made a sub routine to create the chart:

    Sub ChartCreate()
       
        Dim sh As Worksheet
        Dim ChartPu As Chart
       
            Set sh = ActiveWorkbook.Worksheets("Sheet2")
            Set ChartPu = sh.Shapes.AddChart.Chart
           
            With ChartPu
                .ChartType = xlXYScatterSmoothNoMarkers
                .SeriesCollection.NewSeries
                .SetElement (msoElementLegendTop)
                .Parent.Height = Application.CentimetersToPoints(8)
                .Parent.Width = Application.CentimetersToPoints(20)
            End With

    End Sub

    And I want to make another sub routine to refer to that particular chart and provide the data range as well as modify it depending on selected data... Anybody could help?

    Wednesday, December 17, 2014 9:47 AM

Answers

  • Hi GyTasS,

    One way is to add a chart title to the chart, then use this title to determine if it's what you want. Check this VBA code sample:

    Sub ChartCreate()
        Dim sh As Worksheet
        Dim ChartPu As Chart
        
        Set sh = ActiveWorkbook.Worksheets("Sheet2")
        Set ChartPu = sh.Shapes.AddChart.Chart
        
        With ChartPu
            .HasTitle = True
            .ChartTitle.Text = "MyChart"
            .ChartType = xlXYScatterSmoothNoMarkers
            .SeriesCollection.NewSeries
            .SetElement (msoElementLegendTop)
            .Parent.Height = Application.CentimetersToPoints(8)
            .Parent.Width = Application.CentimetersToPoints(20)
        End With
    End Sub
    
    Sub SelectMyChart()
        Dim sh As Worksheet
        Dim ChartPu As Chart
        
        Set sh = ActiveWorkbook.Worksheets("Sheet2")
        Set ChartPu = GetChartByCaption(sh, "MyChart")
        
        ChartPu.ChartArea.Select
    End Sub
    
    
    Function GetChartByCaption(ws As Worksheet, sCaption As String) As Chart
        Dim myChartObject As ChartObject
        Dim myChart As Chart
        Dim sTitle As String
    
        Set myChart = Nothing
        For Each myChartObject In ws.ChartObjects
            If myChartObject.Chart.HasTitle Then
                sTitle = myChartObject.Chart.ChartTitle.Caption
                If StrComp(sTitle, sCaption, vbTextCompare) = 0 Then
                    Set myChart = myChartObject.Chart
                    Exit For
                End If
            End If
        Next
        Set GetChartByCaption = myChart
        Set myChartObject = Nothing
        Set myChart = Nothing
    End Function
    
    Call the SelectMyChart method, the chart which has title "MyChart" will be selected. Of course you can also use other properties to determine if the chart is what you want, you can modify the GetChartByCaption method as you want.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, December 18, 2014 6:59 AM
    Moderator