 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / October 2007Count the number of changes to a cell
Is there a function to keep track (count) of how many times a particular cell, the date in the cell, has changed? Thanks!!! R It's possible and is fairly complicated but may not be practical in most applications. http://www.oaltd.co.uk/Excel/Default.htm Look for Previous.zip  SignatureBiff Microsoft Excel MVP > Is there a function to keep track (count) of how many times a particular > cell, the date in the cell, has changed? Thanks!!! > > R Event code can do this. Enter a 0 in C1 then copy/paste this code into your worksheet module. Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo enditall Application.EnableEvents = False If Target.Value <> "" Then With Target.Offset(0, 2) .Value = .Value + 1 End With End If enditall: Application.EnableEvents = False End Sub Right-click on the sheet tab and "View Code" Paste into that sheet module. Whe you change A1 C1 will add to the count each time. Gord Dibben MS Excel MVP >Is there a function to keep track (count) of how many times a particular >cell, the date in the cell, has changed? Thanks!!! > >R Gord, I couldn't get this to work properly. It only counted a change once. The counter cell went from 0 to 1 and that's it! You left events disabled at the end. Even turning events back on didn't make a difference. SignatureBiff Microsoft Excel MVP > Event code can do this. > [quoted text clipped - 25 lines] >> >>R Works for me. Enter 0 in C1 and add something to A1 Change A1 and number in C1 increases for me each time a change is made. Thanks for pointing out the not turning avents back on. Damn copy and paste<g> Gord >Gord, I couldn't get this to work properly. > [quoted text clipped - 20 lines] >> Application.EnableEvents = False >> End Sub Well, I must have screwed up something when I first tried this! It works now. Much better than the method I referenced. SignatureBiff Microsoft Excel MVP > Works for me. > [quoted text clipped - 33 lines] >>> Application.EnableEvents = False >>> End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.