Counter to track number of times cell changes

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I am trying to setup a worksheet that has three columns. In cell A2, it is a binary number that is auto-populated by the code; in B2, it is a counter that counts the number of times cell A2 changes; and in C2, I have an if/then formula that outputs a binary number based upon a cell in another worksheet. I've written the VBA code so that when cell C2 is 1 it triggers cell A2 to be filled with 1, and if not, it will be 0. Subsequently, whenever cell A2 is 1, cell B2 will count each instance and keep record of the number of times A2 has been 1. Unfortunately, it seems that cell B2 only recognizes that cell has changed to a 1 when I manually enter 1 in A2 and hit enter. Can someone help me understand why B2 isn't automatically recognizing when cell A2 automatically changes to a 1 (when the code auto-populates it based on the logic in C2)? Thanks so much!

VBA Code:
Private Sub worksheet_Activate()
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = "0" Then
        Range("A2") = "0"
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
    Call Macro2(Target)
End Sub

Sub Macro1(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = "0" Then
        Range("A2") = "0"
    End If
    Application.EnableEvents = True
    Exit Sub
End Sub

Sub Macro2(ByVal Target As Range)
    If Target.Address <> "$A$2" Then Exit Sub
    If UCase(Target) = 1 Then
        Range("B2").Value = Range("B2").Value + 1
    End If
End Sub
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
in the Macro1 you set EnableEvents to false then possibly make a change to A2 then set EnableEvents to true. When you change A2 no Change event occurs because you have disabled events.
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
734
Try this code
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If [a2] = 1 Then
[B2] = [B2] + 1
End If
Application.EnableEvents = True


End Sub
Private Sub worksheet_Activate()
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = 0 Then
        Range("A2") = 0
    End If
End Sub
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Thank you both for the quick response! kvsrinivasamurthy, I implemented the code change you suggested to my macro1 in the code, and now when C2 on Sheet1 is manually changed to a 1, A2 is auto-populated to a 1 and triggers B2 to count the change. However, I am looking to put a formula in C2 that references data from A1 on Sheet2. I'm finding that when C2 on Sheet1 has an if/then formula that changes between 1 and 0, the counter is not recognizing the change; it only seems to work when there is a manual entry of 1 or 0 in Sheet1, C2.

Additionally, I'm finding that when C2 is 1 and I toggle between worksheets, the counter is activated to count when I click back onto sheet1. For example, if C2 on sheet1 is 1 and I toggle to sheet2 to check data, when I toggle back to sheet1 the counter is triggered to count up. Is there a way to also prevent this?

I am attaching a copy of the most recent code, and an image of how the worksheet is structured. Thanks, both!

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
    Call Macro2(Target)
End Sub

Sub Macro1(ByVal Target As Range)
Application.EnableEvents = False
On Error Resume Next
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = "0" Then
        Range("A2") = "0"
    End If
    Application.EnableEvents = True
    Exit Sub
End Sub

Sub Macro2(ByVal Target As Range)
Application.EnableEvents = False
If [a2] = 1 Then
[B2] = [B2] + 1
End If
Application.EnableEvents = True
End Sub
Private Sub worksheet_Activate()
If Range("C2").Value = 1 Then
        Range("A2") = "1"
    ElseIf Range("C2").Value = "0" Then
        Range("A2") = "0"
    End If
End Sub
 
Last edited by a moderator:

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows

ADVERTISEMENT

you didn't get the picture of the worksheet attached. Also, I still wonder about your placement of the EnableEvents statements. I would want to trace the code to see when EnableEvents are off and when on. also, in Macro1, you do not need the Exit Sub at the end.
 

kvsrinivasamurthy

Well-known Member
Joined
Nov 6, 2013
Messages
734
I think It is sufficient to have code such that Whenever C2 changes A2 will change and all changes in A2 ie 0 to 1 and 1 to 0 are to be counted.
C2 is Having formula.
Whenever sheet is activated it should not effect counting.
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi both--Attaching picture of the spreadsheet. I'm still having trouble getting the counter to work when Sheet1 C2 changes between 1 and 0 based upon conditions met from cell A1 on Sheet2. I've also tried to turn the EnableEvents line on and off; however, that doesn't work and seems to crash the code (Excell hangs up). Any recommendations appreciated. Thanks!
 

Attachments

  • Worksheet Image.JPG
    Worksheet Image.JPG
    68.7 KB · Views: 9

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
*drth, I have played around with this a little and have figured somethings out. First, if you turn off the Worksheet_Activate event then you no longer increment B2 when switching between Sheet2 and Sheet1. I will experiment some more with that because of my second point, Worksheet_Change does not fire when a value in a cell changes due to a re-calculate ( see Worksheet.Change event (Excel) ). Inorder to get C2 to trigger a change if it is a formula you will need to find some other mechanism.

I also combined Macro1 and Macro2 and modified Worksheet_Change. Here is the code I am working with now:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Call Macro1(Target)
End Sub

Sub Macro1(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$C$2" Then
  If Range("C2").Value = 1 Then
    Range("A2") = "1"
  ElseIf Range("C2").Value = "0" Then
    Range("A2") = "0"
  End If
ElseIf Target.Address = "$A$2" Then
  If Target.Value = 1 Then
    Range("B2").Value = Range("B2").Value + 1
  End If
End If
End Sub
This works when C2 is not a formula but as I said above, if C2 is a formula it does not trigger a Change event. I will continue experimenting for a solution.
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
I just found some other ideas. Here is my lastest code:

VBA Code:
Option Explicit
Dim HoldC2Value As Variant

Private Sub worksheet_Activate()
  If Range("C2").Value <> HoldC2Value Then
    Range("A2") = Range("C2").Value
  End If
End Sub

Private Sub Worksheet_Deactivate()
  HoldC2Value = Range("C2").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
  If Target.Value = 1 Then
    Range("B2").Value = Range("B2").Value + 1
  End If
End If
End Sub

Note I collapsed the two macros into the Worksheet_Change event. I added a Worksheet_Deactivate event and a variable HoldC2Value. See how it works for you
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Thank you for your help, vw412! I dropped in the optimized code you provided. I noticed that it was missing an if/then condition I had in the original code as Macro1, so added it to the code attached. I'm still facing the issue that the counter is not registering a change in A2 (auto-populated as 1 or 0 by the script when the formula in C2 changes). Is there any way for the counter to recognize the change in A2? Technically, there is no "official" formula in A2 and is populated by the macro to a 1 or 0 based upon an if/then formula in C2. I would imagine that the worksheet would pickup the change in A2 from 1 to 0 since the content of the cell is changing.

VBA Code:
Option Explicit
Dim HoldC2Value As Variant

Private Sub worksheet_Activate()
  If Range("C2").Value <> HoldC2Value Then
    Range("A2") = Range("C2").Value
  End If
End Sub

Private Sub Worksheet_Deactivate()
  HoldC2Value = Range("C2").Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Address = "$C$2" Then
  If Range("C2").Value = 1 Then
    Range("A2") = "1"
  ElseIf Range("C2").Value = "0" Then
    Range("A2") = "0"
  End If
ElseIf Target.Address = "$A$2" Then
  If Target.Value = 1 Then
    Range("B2").Value = Range("B2").Value + 1
  End If
End If
End Sub
 

Forum statistics

Threads
1,141,062
Messages
5,704,054
Members
421,325
Latest member
tapete86

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