• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / VBA / Animate a Chart in Excel

Animate a Chart in Excel

Animate a chart in Excel to create a cool effect for your charts. You will see the chart build itself in front of your very eyes.

The chart can be created in the usual way and then VBA is used to create the animation effect.

In this example a combo box is used to provide a way for the user to select the chart they want to see. The chart then gradually appears one data point at a time.

The VBA used to Animate the Chart

The code below is used to animate the chart from the change event of the combo box.

Private Sub cboQtr_Change()
Dim i As Single
Dim QtrNo As Integer
Dim SalesPerson As Integer
Dim SalesValue As Single
Sheets("Calculations").Range("B2:B8").ClearContents
QtrNo = Right(Range("I2").Value, 1)
For SalesPerson = 1 To 7
  SalesValue = Range("C3:F9").Cells(SalesPerson, QtrNo).Value
  For i = 1 To SalesValue Step 3
    Sheets("Calculations").Range("B2:B8").Cells(SalesPerson) = i
    DoEvents
  Next i
Next SalesPerson
End Sub

The Explanation

Four variables are used in the code.

  • One for looping through each salesperson in the table
  • One to store the Qtr selected by the used from the combo box
  • One for storing the salespersons sales total currently being animated
  • And one for incrementing from 1 to the salesperson sales total creating the animated effect

The Calculations sheet contains the table that the chart is created from. This data is cleared first and then re-created.

The Right function is used to extract the number from the Qtr selected by the user.

There are two loops. One for moving through each salesperson in turn. Then an interior loop to increment a value up to the salesperson total.

This code steps 3 at a time. This value can be increased to speed the animation up, or decreased to slow it down.

The DoEvents function is used to create a mini pause in the loop each time it iterates. This function is essential to the working of the VBA procedure.

To learn these VBA techniques and a whole lot more, make sure you sign up for our Excel VBA course.

Watch the Video

Related Posts:

  • Create a Picture Lookup in Excel
    Create a Picture Lookup in Excel
  • What You Need to Know About Dynamic Array Formulas in Excel
    What You Need to Know About Dynamic Array Formulas in Excel
  • Lookup Multiple Values in Excel
    Lookup Multiple Values in Excel
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel

Reader Interactions

Comments

  1. Margaret Maoni Tatut says

    11 June 2014 at 2:09 am

    Exceptional

    Reply
  2. Avril says

    25 June 2014 at 12:18 pm

    Thanks for this video! My chart works well however it’s return 9 less on the calculations sheet e.g.
    100 returns 91
    200 returns 191
    300 returns 291, etc.
    And when I enter 9 it returns 1.
    Please help! Thanks.

    Reply
    • computergaga_blog says

      26 June 2014 at 7:08 am

      On the animating a chart spreasheet? I’m not sure what is causing this.

      Reply
      • Avril says

        26 June 2014 at 7:27 am

        It’s happening on the ‘Calculations’ sheet when the $ are automatically updated. I also note, the same discrepancy on your video e.g. in Qtr 4 Sally is 51 on the table but in the chart she is 49. Fred & Karen are off too.

        Reply
  3. Tom C says

    29 November 2014 at 2:50 am

    Love it! Thank you for sharing.

    Reply
  4. dipak says

    3 June 2015 at 3:19 pm

    i want to prepare XY scatter chart for time Vs multiple process

    Reply
  5. John says

    9 May 2021 at 1:13 pm

    My question: In your animation graph, you plotted x- and y-axis as person (or text) vs. value (Qrt.). Can you make an animation plot with values for both x- and y-axis ?

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·