Animated line chart

Try this macro.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
    
Sub Animated_Chart()
    Dim i As Long, Lr As Long
    
    Range("H1").Select
    Lr = Range("B" & Rows.Count).End(xlUp).Row
    
    
    With ActiveSheet.ChartObjects(1).Chart
        .Axes(xlValue).MaximumScale = Int(Application.Max(Range("B2:F" & Lr))) + 1
        For i = 2 To Lr
            .SeriesCollection(1).Values = Range("B" & i).Resize(, 5)
            .ChartTitle.Characters.Text = Format(Range("A" & i), "mmmm yyyy")
            DoEvents
            Sleep 300   'Animation speed: milliseconds/frame
        Next i
    End With
    
End Sub

Thanks for the help....I get this error about 64 bit systems

https://dl.dropboxusercontent.com/u/90210377/error.JPG

Try this. The #IF #Else #Endif statement goes at the very top of the code module in the module's Declarations section.

#If VBA7 And Win64 Then
    ' 64 bit Excel
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongLong)
#Else
    ' 32 bit Excel
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
    
Sub Animated_Chart()
    Dim i As Long, Lr As Long
    
    Range("H1").Select
    Lr = Range("B" & Rows.Count).End(xlUp).Row
    
    With ActiveSheet.ChartObjects(1).Chart
        .Axes(xlValue).MaximumScale = Int(Application.Max(Range("B2:F" & Lr))) + 1
        For i = 2 To Lr
            .SeriesCollection(1).Values = Range("B" & i).Resize(, 5)
            .ChartTitle.Characters.Text = Format(Range("A" & i), "mmmm yyyy")
            DoEvents
            Sleep 300   'Animation speed: milliseconds/frame
        Next i
    End With
    
End Sub

Not sure about how to make a stop animation button. There's probably a clever way to make it happen though.

You can hit the space bar or any character key to stop the code. Note: what you type goes into cell H1 as it's the active cell.

Private m_blnStopAnimation As Boolean
Sub Animated_Chart()
    Dim i As Long, Lr As Long
    
    If m_blnStopAnimation Then
        ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = "Stop Animation"
        m_blnStopAnimation = False
    Else
        ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = "Animate"
        m_blnStopAnimation = True
        Exit Sub
    End If
    
    Range("H1").Select
    Lr = Range("B" & Rows.Count).End(xlUp).Row
    
    With ActiveSheet.ChartObjects(1).Chart
        .Axes(xlValue).MaximumScale = Int(Application.Max(Range("B2:F" & Lr))) + 1
        For i = 2 To Lr
            .SeriesCollection(1).Values = Range("B" & i).Resize(, 5)
            .ChartTitle.Characters.Text = Format(Range("A" & i), "mmmm yyyy")
            DoEvents
            Sleep 300   'Animation speed: milliseconds/frame
            If m_blnStopAnimation Then Exit For

        Next i
    End With
    
End Sub

@Andy Pope
That toggles the button caption, but the button isn't click-able while the code is running. So it doesn't stop the code. What am I missing?

Looks like it needed the button pressed twice in order to get it going.

I've changed the logic and variable name and now it should anitmate on first press. Note I've increased the speed of anitmation.

#If VBA7 And Win64 Then
    ' 64 bit Excel
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongLong)
#Else
    ' 32 bit Excel
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
    
Private m_blnAnimation As Boolean
Sub Animated_Chart()
    Dim i As Long, Lr As Long
    
    If Not m_blnAnimation Then
        ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = "Stop Animation"
        m_blnAnimation = True
    Else
        ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text = "Animate"
        m_blnAnimation = False
        Exit Sub
    End If
    
    Range("H1").Select
    Lr = Range("B" & Rows.Count).End(xlUp).Row
    
    With ActiveSheet.ChartObjects(1).Chart
        .Axes(xlValue).MaximumScale = Int(Application.Max(Range("B2:F" & Lr))) + 1
        For i = 2 To Lr
            .SeriesCollection(1).Values = Range("B" & i).Resize(, 5)
            .ChartTitle.Characters.Text = Format(Range("A" & i), "mmmm yyyy")
            DoEvents
            Sleep 100   'Animation speed: milliseconds/frame
            If Not m_blnAnimation Then Exit For

        Next i
    End With
    
End Sub