Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamically move charts on over time as the data changes

    Hi

    I am using Excel 2010 and I wondered if there was any way to move chart lines dynamically over time.

    So, I have data in a row by date, I then draw a line chart based on that data and to that date, as I add additional columns with the date and more data I want to know if I can dynamically increase the line chart to take that data into consideration with out having to go in and change the data range.

    The reason I want to do this is because I have loads of charts and it is a pain to go into each and change the range.

    Thanks

    Mike

  2. #2
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    Hi

    Thanks for this and sorry for not responding earlier, this look really great but I have an added dimension which not sure how to apply you code to.

    Attached is a cut down example of what I am trying to achieve.

    There are 3 tabs

    1) Reporting Parameters - this defines the start and end dates for the chart data

    2) Totals for the charts - this has rows and rows of data which is the base to the charts, please note that the area's in yellow are taken from pivot tables by fo this example I have just entered actual values. All non yellow areas are summary totals used for the charts, will also be for other charts as well if I can get this working

    3) Total Chart All, CHN, PLN, AND - this has the charts based on the data in 2) above.

    So the areas I am getting stuck on are:

    1) Get the Chart to change when data is added (which is your code)
    2) Doing it for all the charts as they take data from different points in the tab 2) above

    I have been trying to work on a macro which creates the data range and updating it but it is becoming messy and my macro skills are failing me... that was not too difficult.

    Any suggestions would be great... you may even find it to be a challenge

    Many thanks

    Mike
    Attached Files Attached Files

  4. #4
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts
    Mike,

    I revised your file with code that is initiated from a button placed on the "Reporting Parameters" page. Once you enter in your start and end dates, click the button and your charts will be updated reflecting the start and end dates in the X axis and their perspective values.

    Hope this is what you are looking to do.

    Maud

    Code:
    Sub UpdateChart()
    Application.ScreenUpdating = False
    '----------------------------------------
    'DECLARE VARIABLES
    Dim Parameters As Worksheet
    Dim Chrt As Worksheet
    Dim Totals As Worksheet
    Dim Data As Range
    Dim Data1 As Range
    Dim Data2 As Range
    Dim Data3 As Range
    Dim Data4 As Range
    Dim Data5 As Range
    Dim Data6 As Range
    Dim Data7 As Range
    Dim Data8 As Range
    '---------------------------------------
    'SET WORKSHEETS
    Set Parameters = Worksheets("Reporting Parameters")
    Set Chrt = Worksheets("Totals Chart All, CHN, PLN, AND")
    Set Totals = Worksheets("Totals for the Charts")
    '---------------------------------------
    'VALIDATE DATES
    If Parameters.Range("C2") < Parameters.Range("C1") Then
        MsgBox "Your end date cannot be prior to the start date"
        Exit Sub
    End If
    '---------------------------------------
    'SET SERIES RANGES
    Totals.Activate
    Set Data = Totals.Range(Cells(1, Parameters.Range("C1")), Cells(1, Parameters.Range("C2")))      'X VALUES
    Set Data1 = Totals.Range(Cells(221, Parameters.Range("C1")), Cells(221, Parameters.Range("C2"))) 'SERIES 1 CHART 6
    Set Data2 = Totals.Range(Cells(222, Parameters.Range("C1")), Cells(222, Parameters.Range("C2"))) 'SERIES 2 CHART 6
    Set Data3 = Totals.Range(Cells(69, Parameters.Range("C1")), Cells(69, Parameters.Range("C2")))   'SERIES 1 CHART 7
    Set Data4 = Totals.Range(Cells(70, Parameters.Range("C1")), Cells(70, Parameters.Range("C2")))   'SERIES 2 CHART 7
    Set Data5 = Totals.Range(Cells(140, Parameters.Range("C1")), Cells(140, Parameters.Range("C2"))) 'SERIES 1 CHART 8
    Set Data6 = Totals.Range(Cells(141, Parameters.Range("C1")), Cells(141, Parameters.Range("C2"))) 'SERIES 2 CHART 8
    Set Data7 = Totals.Range(Cells(211, Parameters.Range("C1")), Cells(211, Parameters.Range("C2"))) 'SERIES 1 CHART 9
    Set Data8 = Totals.Range(Cells(212, Parameters.Range("C1")), Cells(212, Parameters.Range("C2"))) 'SERIES 2 CHART 9
    '---------------------------------------
    'UPDATE CHARTS
    Chrt.Activate
    'CHART 6
        ActiveSheet.ChartObjects("Chart 6").Activate
        ActiveChart.SeriesCollection(1).XValues = Data
        ActiveChart.SeriesCollection(1).Values = Data1
        ActiveChart.SeriesCollection(2).Values = Data2
    'CHART 7
        ActiveSheet.ChartObjects("Chart 7").Activate
        ActiveChart.SeriesCollection(1).XValues = Data
        ActiveChart.SeriesCollection(1).Values = Data3
        ActiveChart.SeriesCollection(2).Values = Data4
    'CHART 8
        ActiveSheet.ChartObjects("Chart 8").Activate
        ActiveChart.SeriesCollection(1).XValues = Data
        ActiveChart.SeriesCollection(1).Values = Data5
        ActiveChart.SeriesCollection(2).Values = Data6
    'CHART 9
        ActiveSheet.ChartObjects("Chart 9").Activate
        ActiveChart.SeriesCollection(1).XValues = Data
        ActiveChart.SeriesCollection(1).Values = Data7
        ActiveChart.SeriesCollection(2).Values = Data8
    '--------------------------------------
    'CLEANUP
    Set Parameters = Nothing
    Set Chrt = Nothing
    Set Totals = Nothing
    Set Data = Nothing
    Set Data1 = Nothing
    Set Data2 = Nothing
    Set Data3 = Nothing
    Set Data4 = Nothing
    Set Data5 = Nothing
    Set Data6 = Nothing
    Set Data7 = Nothing
    Set Data8 = Nothing
    Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-07-27 at 12:16.

  5. #5
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hey Maud

    That is fantastic, will play with this.

    Many thanks

    Mike

  6. #6
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Couple of questions please.

    1) Can I pick out if a chart number is not being used, the reason for the question is that if I (or anybody else) adds charts to that tab they may not know the chart number and the rows whcih the data is in. I was planning to put a loop in going through charts 1 to 20 and using the resume next to skip any errors
    2) If a chart is present can I find out the data range row number so I do not have to predefine row numbers
    3) Last but not least I will have some charts which are based on multi lines of data and not in one block.

    Thanks again and sorry to be a pain.

    Regards

    Mike

  7. #7
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Maud,

    Thanks for your help I have now cracked it and the macro works well and all parameterised.

    In the end I worked using the Xformula option instead of the Xvalues.

    Regards


    Mike

  8. #8
    Bronze Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,280
    Thanks
    46
    Thanked 250 Times in 230 Posts
    If a chart is present can I find out the data range row number so I do not have to predefine row numbers
    mikeyt

    Here is the code to find the data ranges for the selected chart

    datarange.png

    Code:
    Public Sub FindSeries()
    On Error GoTo errorhandler
    '---------------------------------
    'DECLARE AND SET VARIABLES
    Dim Chrt As Chart
    Dim DataRange As String
    Dim Series As Integer
    Dim Xvalues As String
    Set Chrt = ActiveChart
    Xvalues = ""
    '---------------------------------
    'CYCLE THROUGH CHART SERIES AND FIND RANGES
    For Series = 1 To 2
        DataRange = Chrt.SeriesCollection(Series).Formula
        s = Split(DataRange, ",")
        If Xvalues = "" Then
            t = Split(s(1), "!")
            Xvalues = t(1)
        End If
        u = Split(s(2), "!")
        Message = Message & _
               "Series " & Series & ":" & Chr(13) & _
               "   Data Range: " & u(1) & Chr(13)
    Next Series
    MsgBox "X value Range: " & Xvalues & Chr(13) & Message
    Exit Sub
    '---------------------------------
    'NO CHART SELECTED
    errorhandler:
    MsgBox "Please select a chart then run the code again"
    End Sub

  9. #9
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    224
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks

Posting Permissions

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