triggers don't update as expected in vba-CodePudding

I've written some code that deals with triggers. the main idea is to create dynamic triggers to cells of a sheet and based on the value of the cell the counter add or subtract from the total selected. Let me present it with an example and the code to make it clearer: This is how my excel sheets appear.

A B C D E
1 
2
3

to limit the scope, i have 4 columns and 3 rows, i want to assign triggers to A1, B1, C1 D1, E1 and more. therefore i wrote this that dynamically assign triggers to the cells.

    For j = 1 To 3  ' row
        For I = 1 To 4 ' columns
            If Target.Column = I And Target.Row = j Then
                If Target.Value = "Yes" Then
                    Yes_4 = Yes_4   1
                    
                ElseIf Target.Value = "No" Then
                    No_4 = No_4   1
    
                ElseIf Target.Value = "Not applicable" Then
                    Not_Applicable_4 = Not_Applicable_4   1
    
                End If
                If Target.Value = "Green - Sufficient" Then
                    Green_4 = Green_4   1
                ElseIf Target.Value = "Orange - Largely sufficient with points for follow-up" Then
                    Orange_4 = Orange_4   1
                ElseIf Target.Value = "Red - Insufficient" Then
                    Red_4 = Red_4   1
                End If
     
                Range("O" & j).Value = "The ratings you selected are as follows:"   vbNewLine   "Yes: " & Str(Yes_4)   vbNewLine   "No: " & Str(No_4)   vbNewLine   "Green: " & Str(Green_4) _
                      vbNewLine   "Orange: " & Str(Orange_4)   vbNewLine   "Red: " & Str(Red_4)   vbNewLine   "No Applicable: " & Str(Not_Applicable_4)
            End If
        Next I
    Next j

If the filled value of the cell is "Yes", the yes variable becomes yes 1. not only for one cell, for every cell in the row, the summary is given in column O. for example: yes= 1, no =3, gree= 2 and so on. it functions well. The problem is that when a "Yes" is selected in a cell, the value in column O shows that yes = 1, but if you change your mind and change it from "Yes" to "No", the value in column O shows that yes = 1, no =1. Instead of "No" = 1 and "Yes" = 0.

CodePudding user response：

Assign formulas to the result cells that reference the appropriate range, with the appropriate value, such as...

=countif(your range spec, "Yes")
=countif(your range spec, "Green")

If you need to adjust the range spec based on data conditions, you can update that formula in VBA reacting to the size of the range. Otherwise you don't need vba to compute the totals.

CodePudding user response：

If you add a global variable to record the current value of the cell on SelectionChange, you can then check for changes and decrease the counter accordingly.

Dim sPrevious as string

Public Sub Worksheet_SelectionChange(ByVal Target As Range)
    sPrevious = Target.Value
End Sub

Then add into the existing Change event:

Private Sub Worksheet_Change(ByVal Target As Range)

  For j = 1 To 3  ' row
        For I = 1 To 4 ' columns
            If Target.Column = I And Target.Row = j Then

                If sPrevious <> Target.Value Then ''check if value has changed
                    If sPrevious = "Yes" Then
                        yes_4 = yes_4 - 1
                    ElseIf sPrevious = "No" Then
                        no_4 = no_4 - 1
                    ElseIf sPrevious = "Not applicable" Then
                        Not_Applicable_4 = Not_Applicable_4 - 1
                    End If
                    If sPrevious = "Green - Sufficient" Then
                        Green_4 = Green_4 - 1
                    ElseIf sPrevious = "Orange - Largely sufficient with points for follow-up" Then
                        Orange_4 = Orange_4 - 1
                    ElseIf sPrevious = "Red - Insufficient" Then
                        Red_4 = Red_4 - 1
                    End If
                End If