by Allen Wyatt
(last updated March 28, 2020)
Ray would like, if possible, a way to animate a "count up" for a value in a cell. For instance, in cell B7 he might have a value of 23. He would like to reference that value in cell E4 and have cell E4 count up from 0 to 23 (0, 1, 2, 3, etc.), showing each number in turn.
There is no built-in way to do this in Excel, but you can use a macro to do the animation. Basically, the macro would need to find out what is in cell B7, and then use a For...Next loop to step through the values between 0 and whatever is in B7. During each iteration of the loop, the value in E4 is changed and some sort of delay is introduced.
The delay portion of the macro is what actually provides the ability to vary how the macro does its work. The delay is necessary to make the animation seem to work; without it, the numbers in E4 would increment too quickly. Excel provides a couple of handy ways to implement the delay. For instance, this example of the macro relies on the Sleep function:
#If VBA7 Then Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #Else Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) #End If Private Sub Worksheet_Change(ByVal Target As Range) Dim n As Integer Const cellWatch As String = "$B$7" Const cellCount As String = "$E$4" Const msec As Long = 200 ' milliseconds If Target.Address = cellWatch Then Application.EnableEvents = False Range(cellCount).Show If IsNumeric(Target) Then For n = 0 To Target ' skipped if Target < 0 Range(cellCount) = n Sleep msec ' delay each increment Next n End If Range(cellCount) = Target Application.EnableEvents = True End If End Sub
This code should be added to the ThisWorksheet module, as it is designed to run every time something changes in the worksheet. The code checks to see if the cell being changed is the target cell (B7). If it is, then it grabs the value there and jumps into a For...Next loop that updates whatever is in cell E4. The Sleep function is used to delay, in this instance, 200 milliseconds between each update of E4.
If you want a macro that is shorter and doesn't rely on the Worksheet_Change event handler, you might consider the following. It utilizes the Wait method to pause in the For...Next loop:
Sub CountUp() Dim J As Integer For J = 0 To Range("B7").Value Range("E4").Value = J Application.Wait (Now + TimeValue("0:00:01")) Next J Range("E4").Value = Range("B7").Value End Sub
This version of the macro pauses a full second between each update to cell E4.
Whenever you use a macro like this that implements some sort of delay, remember that your worksheet may seem less responsive. This is because of that delay and however large the value is that is in cell E4. (The larger the value, the longer the aggregate delay.)
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13753) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Normally Excel positions a UserForm in the center of your screen. You may want to position the form elsewhere, more ...Discover More
Excel allows you to hide worksheets so that they aren't visible to those using your workbook. Hiding worksheets has a ...Discover More
You can use macros to make your common Excel tasks easier and faster. For instance, if you routinely need to create new ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.