Results 1 to 5 of 5

Thread: (VBA) Creating a Counter on Cell Change
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default (VBA) Creating a Counter on Cell Change

    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

  2. #2
    Legend VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    8 Thread(s)

    Default Re: (VBA) Creating a Counter on Cell Change

    What happens when B1 gets to 50+
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (VBA) Creating a Counter on Cell Change

    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.

  4. #4
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,671
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    1 Thread(s)

    Default Re: (VBA) Creating a Counter on Cell Change

    MW,

    Might this be of use......

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

    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.
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  5. #5
    New Member
    Join Date
    Sep 2014
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: (VBA) Creating a Counter on Cell Change

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


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •