Written by Allen Wyatt (last updated April 15, 2024)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365
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.)
Note:
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 Microsoft 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to use a macro to select a specific cell in a different workbook? It's not as straightforward of a proposition as ...
Discover MoreMacros allow you to perform all sorts of file-related operations. One such operation allows you to delete a directory. ...
Discover MoreWhen working with colors in Excel you can specify them using either RGB or HSL values. Converting from HSL to RGB can be ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-03-28 15:29:06
I tried this method to update a graph, but it did not work. Only after VBA stopped running (e.g., with STOP) would the graph update.
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments