locked
How to identify or refer to the chart RRS feed

  • 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:46 AM

Answers

  • You can clear the series before populating.

    Sub UseChart()
        Dim ChartPu As Chart
        Dim TimeCells As Range
        Dim PuCells As Range
        Dim TreatCells As Range
    
        With ActiveWorkbook.Worksheets("Sheet2")
            Set ChartPu = .ChartObjects("MyChart").Chart
        End With
    
            Set TimeCells = Worksheets("Sheet1").Range("Time")
            Set PuCells = Worksheets("Sheet1").Range("Puexit")
            Set TreatCells = Worksheets("Sheet1").Range("ExitTreatmentTime")
       
            With ChartPu
                Do While .SeriesCollection.Count > 0
                    .SeriesCollection(1).Delete
                Loop
            
                With .SeriesCollection.NewSeries
                    .Name = "=""PU"""
                    .XValues = TimeCells
                    .Values = PuCells
                End With
                
                With .SeriesCollection.NewSeries
                    .Name = "=""Treatment Time"""
                    .XValues = TimeCells
                    .Values = TreatCells
                    .AxisGroup = 2
                End With
                
                With .Axes(xlCategory)
                    .MinorUnit = 0.04
                    .MajorUnit = 0.05
                    .TickLabels.Orientation = 45
                End With
                .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
                .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Period of time"
            End With
    End Sub
    


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by GyTasS Wednesday, December 17, 2014 1:46 PM
    Wednesday, December 17, 2014 1:06 PM
  • You need to fully qualify the cell references in order to use the associated WITH command. Without the leading . (period) Cells will reference the active sheet.

        With Sheets("Sheet1")
            If .Cells.AutoFilter Then
               .Cells.AutoFilter
            End If


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by GyTasS Thursday, December 18, 2014 8:32 AM
    Thursday, December 18, 2014 8:27 AM
  • Perfect! I helped me enormously!

    Thank you very much!

    • Marked as answer by GyTasS Thursday, December 18, 2014 8:32 AM
    Thursday, December 18, 2014 8:31 AM

All replies

  • couple of ways, either use the chartobjects collection to index the last chart or by it's name.

    Sub UseChart()
    
        Dim ChartPu As Chart
       
        With ActiveWorkbook.Worksheets("Sheet2")
            Set ChartPu = .ChartObjects(.ChartObjects.Count).Chart
            
            Set ChartPu = .ChartObjects("MyChart").Chart
        End With
    
    End Sub
    

    The order within the collection is based on z-order of objects.

    If using the name reference you will need to name the chart in your create routine

            With ChartPu
                .Parent.Name = "MyChart"
                .ChartType = xlXYScatterSmoothNoMarkers
    


    Cheers,

    Andy
    www.andypope.info

    Wednesday, December 17, 2014 10:35 AM
  • Hey, thanks, but what goes where? I am confused already... So the Sub routine, as I posted creates chart. Do I have to modify this routine first?

    Because my second sub routine is (But it does not work):

    Sub UseChart()
        Dim ChartPu As Chart
        Dim TimeCells As Range
        Dim PuCells As Range
        Dim TreatCells As Range

        With ActiveWorkbook.Worksheets("Sheet2")
            Set ChartPu = .ChartObjects("MyChart").Chart
        End With

            Set TimeCells = Worksheets("Sheet1").Range("Time")
            Set PuCells = Worksheets("Sheet1").Range("Puexit")
            Set TreatCells = Worksheets("Sheet1").Range("ExitTreatmentTime")
       
            With ChartPu
                .Parent.Name = "MyChart"
                .SeriesCollection(1).Name = "=""PU"""
                .SeriesCollection(1).XValues = TimeCells
                .SeriesCollection(1).Values = PuCells
                .SeriesCollection(2).Name = "=""Treatment Time"""
                .SeriesCollection(2).XValues = TimeCells
                .SeriesCollection(2).Values = TreatCells
                .SeriesCollection(2).AxisGroup = 2
                .Axes(xlCategory).MajorUnit = 0.2
                .Axes(xlCategory).MajorUnit = 0.01 
                .Axes(xlCategory).MinorUnit = 0.04
                .Axes(xlCategory).MajorUnit = 0.1
                .Axes(xlCategory).MajorUnit = 0.2
                .Axes(xlCategory).MajorUnit = 0.05
                .Axes(xlCategory).TickLabels.Orientation = 45
                .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
                .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Period of time"
            End With
    End Sub

    Wednesday, December 17, 2014 11:44 AM
  • If you use the name approach you need to name the chart when you create it, so you add the code snippet to you original routine

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

    The limitation with the name approach is that you can only have 1 chart with that name, otherwise you can not be sure which chart is actually being referenced. So this may mean you need to add code for a unique name to assign to the chart.


    Cheers,

    Andy
    www.andypope.info

    Wednesday, December 17, 2014 11:50 AM
  • Thanks! And could you please take a look at the second routine, because, it is not working somehow...

    I get run-time error "Invalid Parameter" At the line:

     .SeriesCollection(1).Name = "=""PU"""

    Any Idea?

    Wednesday, December 17, 2014 11:55 AM
  • I assume the chart actually contains at least 2 data series when that code runs?

    Although there is nothing in your code to suggest any data has been defined in the chart.


    Cheers,

    Andy
    www.andypope.info

    Wednesday, December 17, 2014 12:02 PM
  • Thank Andy,

    I have already find out... One more question, how can I clear the chart contents and formatting using VBA code?

    The idea is to create Chart by one sub routine, then use second one to clean the chart and load the data series, because I want to execute the second routine with different data series and I want to have clean chart every time before executing the second routine...

    Thanks!

    Wednesday, December 17, 2014 1:00 PM
  • You can clear the series before populating.

    Sub UseChart()
        Dim ChartPu As Chart
        Dim TimeCells As Range
        Dim PuCells As Range
        Dim TreatCells As Range
    
        With ActiveWorkbook.Worksheets("Sheet2")
            Set ChartPu = .ChartObjects("MyChart").Chart
        End With
    
            Set TimeCells = Worksheets("Sheet1").Range("Time")
            Set PuCells = Worksheets("Sheet1").Range("Puexit")
            Set TreatCells = Worksheets("Sheet1").Range("ExitTreatmentTime")
       
            With ChartPu
                Do While .SeriesCollection.Count > 0
                    .SeriesCollection(1).Delete
                Loop
            
                With .SeriesCollection.NewSeries
                    .Name = "=""PU"""
                    .XValues = TimeCells
                    .Values = PuCells
                End With
                
                With .SeriesCollection.NewSeries
                    .Name = "=""Treatment Time"""
                    .XValues = TimeCells
                    .Values = TreatCells
                    .AxisGroup = 2
                End With
                
                With .Axes(xlCategory)
                    .MinorUnit = 0.04
                    .MajorUnit = 0.05
                    .TickLabels.Orientation = 45
                End With
                .SetElement (msoElementPrimaryCategoryAxisTitleAdjacentToAxis)
                .Axes(xlCategory, xlPrimary).AxisTitle.Text = "Period of time"
            End With
    End Sub
    


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by GyTasS Wednesday, December 17, 2014 1:46 PM
    Wednesday, December 17, 2014 1:06 PM
  • Thank You! Works perfect!
    Wednesday, December 17, 2014 1:46 PM
  • Hey, Andy once again, do you know why my autofilter is not working when ActiveSheet is either 2 or 3? It works only when I have activated Sheet1... I want to have a code, that after filtering the data and using it to plot a chart, I could again filter the data using different criteria and again plot chart... I need that after using Autofilter and plotting data which I need, Autofilter would be again deactivated for using it again.

    Any suggestions and corrections of my code?

    I am still fairly green at VBA....

    This is my code:

    Sub AutoFiler()
        Dim LastRow As Integer
        Dim LastCol As Integer
        Dim starttime As Date
        Dim starttimestr As String
        Dim endtime As Date
        Dim endtimestr As String
        Dim TimeCells As Range
        Dim ValueCells As Range
        Dim stime As String
        Dim etime As String
       
        stime = InputBox("Enter Start Time")
        etime = InputBox("Enter End Time")
       
        With Sheets("Sheet1")
            If Cells.AutoFilter Then
               Cells.AutoFilter
            End If

            '.Columns("B:B").NumberFormat = "dd-mm-yyyy hh:mm"
            LastRow = .Range("A" & Rows.Count).End(xlUp).Row
            LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
            starttime = DateValue(stime) + TimeValue(stime)
            starttimestr = Format(starttime, "m/d/yyyy hh:mm")
            endtime = DateValue(etime) + TimeValue(etime)
            endtimestr = Format(endtime, "m/d/yyyy hh:mm")

            .Range(.Range("A1"), .Cells(LastRow, LastCol)).AutoFilter _
               Field:=2, _
               Criteria1:=">=" & starttimestr, _
               Operator:=xlAnd, _
               Criteria2:="<=" & endtimestr

    Wednesday, December 17, 2014 1:56 PM
  • You need to fully qualify the cell references in order to use the associated WITH command. Without the leading . (period) Cells will reference the active sheet.

        With Sheets("Sheet1")
            If .Cells.AutoFilter Then
               .Cells.AutoFilter
            End If


    Cheers,

    Andy
    www.andypope.info

    • Marked as answer by GyTasS Thursday, December 18, 2014 8:32 AM
    Thursday, December 18, 2014 8:27 AM
  • Perfect! I helped me enormously!

    Thank you very much!

    • Marked as answer by GyTasS Thursday, December 18, 2014 8:32 AM
    Thursday, December 18, 2014 8:31 AM