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
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
Can you describe the process you are doing (or want to do) to produce the results? I am trying to determine what action will trigger the update of A which will update B. I used the Activate/Deactivate events because you had said you were switching between Sheet1 and Sheet2. It seems that is not what is happening. So to help you I need to go back and get more detail on the process. This isn't a problem. Sometimes when the things we thought would work don't we need to go back and examine the problem and our assumptions to find the solution. This is solvable, just takes some more work.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Thanks for bearing with me, vw412. Greatly appreciate your help in cracking this problem. I'm attaching a couple images that demonstrates how the routine should work. It starts with a change in status data contained in Sheet2, column A, which then triggers the VBA routine to kick-in on Sheet1 when the formulas contained in column C change between binary classifiers. I think this graphical representation might make it easier to follow the output I'm trying to achieve. Hopefully, this helps.
 

Attachments

  • Sheet2.JPG
    Sheet2.JPG
    102 KB · Views: 3
  • Sheet1.JPG
    Sheet1.JPG
    131.6 KB · Views: 3

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
*drth, If I am interpretting this correctly, you are not necessarily switching between sheet 2 and sheet 1 when a change is made on sheet 2, correct? what is the formula in C on sheet 1? Also the formula or user interaction with sheet 2 column A? I grasp how the two sheets and the four columns are to interact but I need to understand how the user interacts with the workbook.
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
That is correct. I am not switching between sheet1 and sheet 2 when a change is made on sheet2. I'd like for both sheets to be automated in the end. The screen grabs of the worksheets are examples of how I'd like the mechanics of the worksheets to work eventually, but the workbook for the most part is automated. The formula in Sheet1, Cell C2 is as follows: =IF(Sheet2!A2="Yes; above pivot point",1,0). Additionally, cells in column A, Sheet2 will eventually have formulas in them that output a result based on conditions in other cells within that worksheet (also automated). In general, there will be little user interaction on sheet2, and no user interaction on sheet1.
 

vw412

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

ADVERTISEMENT

ok. that helps me understand how to trigger the events to get it to do what you want. I will need a couple days to work on it but I think we can do it. if there was a button on sheet 2 that you click to update after changing column A, would that work for you? (Just weighing options and getting your thoughts)
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Greatly appreciate any solutions you might have! I’ve been working on the problem for awhile now and have come to a roadblock with my limited vba knowledge. The data feeding into column A is continuously changing so a clickable button wouldn’t work in this situation. It would need to be an automated feature that initiates counting when the workbook is open.
 

drth

New Member
Joined
Apr 16, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
Hi vw412, wondering if you may have stumbled onto any new ideas on how to approach the problem? Appreciate your help!
 

vw412

Active Member
Joined
Dec 16, 2011
Messages
343
Office Version
  1. 2019
  2. 2016
  3. 2010
  4. 2007
Platform
  1. Windows
*drth, You say sheet 1 is fully automated and sheet 2 is almost all automated. You also say that column A on sheet 2 is continuously changing. What is causing this changing process? Since you are striving for as much automation as possible with minimal to no user interaction, I need to delve into other aspects of the workbook to find triggers to monitor. Could you send me a sanitized (data-wise not functionality) workbook so I could understand to processes you are using? If you can, my email is:

vw412 at vwandsw dot net
 

Forum statistics

Threads
1,136,909
Messages
5,678,520
Members
419,768
Latest member
eguechi09x

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