(VBA) Creating a Counter on Cell Change

MWhiteside

New Member
Joined
Sep 9, 2014
Messages
3
Hi All,

Is it possible to develop VBA that once a given cell changes value the code creates a counter that counts from 1-50 in a different cell?

Example

A1 = 1
B1 = 1-50

If cell A1 changes to 2 (or any value really) cell B1 starts counting up from 1 to 50. Everytime A1 changes B1 restarts the count.

I'm trying to tie the counter to values that drive a series of charts. As the counter moves up the charts would animate. I found code to create animated charts but it's very taxing to hook the code up to a full dashboard. A simple counter seems much easier but I've never created something like this before.

Thank you
 

MWhiteside

New Member
Joined
Sep 9, 2014
Messages
3
I was thinking it would always stop at 50. The code would count 1,2,3,4....50 and stop. The idea, and this may not be possible, is if you could set where the counter stopped you could increase/decrease the time it takes to reach the end.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
4,671
MW,

Might this be of use......

Paste code to your sheet's code module. Right click sheet tab >>> View Code >> Paste to code pane.

Rich (BB code):
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)




Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1")) Is Nothing Then  'A1 is trigger cell
Application.EnableEvents = False
Range("B1") = 0  'B1 is counter cell
For c = 1 To Range("D1")  'edit range D1 = max count
Range("B1") = Range("B1") + 1
Sleep (500)  'edit interval miliseconds
Next c
Application.EnableEvents = True
End If
End Sub
Hope that helps.
 

MWhiteside

New Member
Joined
Sep 9, 2014
Messages
3
Yes sir that is what I was looking to accomplish. I wasn't able to figure out how to set the intervals. Thank you.

:)
 

Forum statistics

Threads
1,078,447
Messages
5,340,345
Members
399,370
Latest member
salamon

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top