(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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

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
5,561
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

:)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,939
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top