Animated Chart using a Timer

[gweasley]

Hello all,

I'm experimenting with chart animation using a timer. In this case, the data displayed on the chart depends on

1. a dropdown box that determines what dataset to get the source data from
2. a group of checkboxes that determine which series to display

I already have the code working just fine scrolling through the items in the drop down every 5 seconds. What's not working is that, 5 seconds after the drop down selection is changed, i'd like the first checkbox to get selected, and after another 5 seconds, the second checkbox, and so on.

This is my code:

Sub AnimateRegions()

Dim AnimationinProgress As Boolean
Dim r As Long, i As Long
Dim starttime

If AnimationinProgress Then
        AnimationinProgress = False
        End
    End If

    AnimationinProgress = True

    UnCheckAll

    For r = 1 To 10         'Number of items in the drop down box
        starttime = Timer
        Range("GCQIndex") = r 'Change the selection in the dropdown box through the linked cell
        Compare
        Do While Timer < starttime + 5
            DoEvents
        Loop
        Range("$A$4").Value = Timer
        
        Select Case Timer
            Case starttime + 5
                Range("$A$25").Value = Timer
                Range("Compare!ShowCentral").Value = True
            Case starttime + 10
                Range("$A$25").Value = Timer
                Range("Compare!ShowNE").Value = True
            Case starttime + 15
                Range("$A$25").Value = Timer
                Range("Compare!ShowWest").Value = True
            Case starttime + 20
                Range("$A$25").Value = Timer
                Range("Compare!ShowSouth").Value = True
        End Select
        If r <> 10 Then UnCheckAll Else CheckAll
    Next r
    
    AnimationinProgress = False
End Sub

Would appreciate your help. I'm doing this to expand my horizons in the Excel Charting/VBA world :D

Thanks!

[gweasley]

Quick explanation: this chart depends on what is currently selected in a dropdown box and then the checkboxes which are linked to my selected cells that determine whether the corresponding checkboxes are selected or not.
This chart will scroll through each question and then check each checkbox one at a time, and then go to the next question in the dropdown.

To the excel gurus on here, pardon my oft found confused stupidity I'm learning! :D

To those who are interested, here is my code:

Sub AnimateRegions()

Dim AnimationinProgress As Boolean
Dim r As Long, i As Long
Dim starttime, finish, finish2, finish3, finish4, finish5

If AnimationinProgress Then
        AnimationinProgress = False
        End
    End If

    AnimationinProgress = True

    UnCheckAll

    For r = 0 To 9         'Number of items in the drop down box
        starttime = Timer
        Sheets("Compare").ComboBox1.ListIndex = r
        Compare
        Do While Timer < starttime + 3
            DoEvents
        Loop
        finish = Timer
        
        Do While Timer < finish + 3
            DoEvents
        Loop
        Range("Compare!ShowCentral").Value = True
        finish2 = Timer
        
        Do While Timer < finish2 + 3
            DoEvents
        Loop
        Range("Compare!ShowNE").Value = True
        finish3 = Timer
        
        Do While Timer < finish3 + 3
            DoEvents
        Loop
        Range("Compare!ShowSouth").Value = True
        finish4 = Timer
        
        Do While Timer < finish4 + 3
            DoEvents
        Loop
        Range("Compare!ShowWest").Value = True
        finish5 = Timer
        
        Do While Timer < finish5 + 3
            DoEvents
        Loop
        
        If r <> 9 Then UnCheckAll Else CheckAll
    
    Next r
    
    AnimationinProgress = False
End Sub

[gweasley]

Question to this - running this animation basically uses up all the system resources and I have to wait till the animation ends. Is there a way to write a macro that would override this or end the animation macro at will short of breaking code execution?

[Andy Pope]

Running a macro will use all the resources. There's no multi threading.

If you can post your workbook I have a quick look see.

[gweasley]

Here it is, Thanks, Andy!!!

[Andy Pope]

I have written 2 routines.
One to control the stop/starting of animation. The other settings the required data.
It uses the OnTime method so the code is running for the shortest possible time.

[gweasley]

Hi Andy,

Thank you so much for your help on this... For some reason, it's not animating in my computer, though the button's caption changes. Anything I should reset/change/set in excel?

Thanks again,

~G

[Andy Pope]

Works for me in xl2k.

You could check to make sure calculation is set to Automatic.
Also maybe add the command DoEvents just before it exits the Updatechart routine.

[gweasley]

I have checked into that and it still doesn't work for some reason. I have also tried wringing the neck of my computer (that part which protrudes from the base of the monitor) to no avail :D Must be an internal excel issue we're having (not the first ).

However, thanks mucho mucho Andy :D