(VBA) Creating a Counter on Cell Change

[MWhiteside]

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

 

[VoG]

What happens when B1 gets to 50+

 

[MWhiteside]

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]

MW,

Might this be of use......

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

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]

Yes sir that is what I was looking to accomplish. I wasn't able to figure out how to set the intervals. Thank you.