Create Animations in Excel (VBA)

Sometimes you are creating a nice workbook for work and realize that you need some sort of animation. Yes you can take the simple route and simply move objects around instantly, but what about times that you really need some sort of movement.

 Let's go ahead and build a simple expanding textbox containing some instructions which we don't want shown all the time. To make this textbox, we will insert a rectangle shape into the worksheet.

You can add some text by clicking the "Text Box" button on the ribbon.

Great! Now we have a neat little textbox. Let's rename it. Click on the Textbox and go to the box to the left of the formula bar. Let's name it "AniBox" (short for animation box).

Now let's find out how big this rectangle is by using the immediate window. Click on your rectangle and go to the immediate window (in the VBE. Get there by pressing Alt + F11) and type ?selection.height

Here, my height is a nice 273. I'll remember that.  Now I'll go back to my rectangle and manually make the height small enough so all you can see is the title.

Now we Right-Click on our rectangle and click "Assign Macro." A dialog box will open up where we can click on "New" on the right side. 

We will be brought to the visual basic editor. Now we can type some code in.  The code is simple. We will run through a for loop to change the size of the rectangle so the height is tall enough to see the instructions (273 in my case, as we found out earlier). 

Before we get too far, we do want to make sure we know what the "Closed" height is. Let's go back to the immediate window and type ?selection.height again after selecting our rectangle.  My height is 30.

Use the code below in your new VBA macro you created. Make sure to change your openHeight and closedHeight properties to whatever your box is. Also, if you want it to go faster, change the howSmooth to a lower number, or if you want it smoother, make it bigger.

Here is the code!  You can also download the example workbook Here: http://hidemyvba.com/AnimationExample.xlsb

 Sub AniBox_Click()  
      'Run the Animation  
      Dim isOpen As Boolean  
      Dim i As Integer  
      Dim endSize As Integer  
      Dim openHeight As Integer  
      Dim closedheight As Integer  
      Dim howSmooth As Integer  
      Dim myTimer  
      'make sure you set these to your open and closed  
      'height as we found using ?selection.height in  
      'the immediate window  
      openHeight = 273  
      closedheight = 30  
      'Make this bigger to have the animation be smoother  
      'but slower, make it small to make the animation  
      'faster I use 15 as a default  
      howSmooth = 15  
      'find out if we need to open or close the box  
      If ActiveSheet.Shapes("AniBox").Height = 30 Then  
           'we need to open it  
           isOpen = False  
           endSize = openHeight  
      Else  
           'we need to close it  
           isOpen = True  
           endSize = closedheight  
      End If  
      'let's actually do the work  
      For i = 1 To howSmooth  
           'now let's change the height  
           If isOpen Then  
                'let's close it  
                ActiveSheet.Shapes("AniBox").Height = _  
                ActiveSheet.Shapes("AniBox").Height - ((openHeight - closedheight) / howSmooth)  
           Else  
                'let's open it  
                ActiveSheet.Shapes("AniBox").Height = _  
                ActiveSheet.Shapes("AniBox").Height + ((openHeight - closedheight) / howSmooth)  
           End If  
           'this allows us to actually see the changes  
           myTimer = Timer  
           Do  
                DoEvents  
           Loop Until Timer - myTimer > 0.01   
      Next i  
      'If someone clicks on the box too many times  
      'we run into errors, so we set the end result here  
      ActiveSheet.Shapes("AniBox").Height = endSize  
 End Sub