Cell Value update due to change in other cell manually only once

Question

I want to increment C49 by 0.8 when there is some change in C50. But I want it to be done only once. I am using following code but increment goes on

Private Sub Worksheet_Change(ByVal Target As Range)

dim x as Integer
x=0

If Not Intersect(Target, Range("C50")) Is Nothing Then

       If Not IsEmpty(Cells(49, 3).Value) Then
            If x = 0 Then
                Cells(49, 3).Value = Cells(49, 3).Value + 0.8
                x = x+1
            End If

        End If

    End If
End Sub

score 0 · Answer 1 · answered Nov 21 '14 at 20:29

0

Try the following VBA code:

Dim PerformChange As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)

If PerformChange Then
    If Not ((Intersect(Target, Range("C50")) Is Nothing) Or _
          (IsEmpty(Cells(49, 3).Value))) Then
        PerformChange = False
        Cells(49, 3).Value = Cells(49, 3).Value + 0.8
    End If
Else
  PerformChange = True
End If

End Sub

The global Boolean PerformChange is used to only allow single changes, otherwise the Change procedure is called recursively.

answered Nov 21 '14 at 20:29

Werner

14,324
7
55
77

Global variable in such a case is not a solution because of two reasons `1` The variable will get reset if the code breaks. `2` If the workbook is saved and closed then the variable gets reset. – Siddharth Rout Nov 21 '14 at 20:31
@SiddharthRout: (1) The code shouldn't break. And if it does, you'll most likely fix it until it doesn't break, from which point on it wouldn't matter. (2) Add updates `PerformChange = True` to the Workbook's `Open` procedure. – Werner Nov 21 '14 at 20:38
The global variable will get reset even if you add an activex control to your worksheet. If a code breaks anywhere else other than the above code, the variable will still get reset :) so `you'll most likely fix it until it doesn't break` this actually doesn't help :) – Siddharth Rout Nov 21 '14 at 20:42
And BTW the variable `PerformChange` above is not `Global`. Global variables are not declared using `Dim` and are declared using `Public` in a module. :) – Siddharth Rout Nov 21 '14 at 20:50

score 0 · Answer 2 · edited May 23 '17 at 10:33

Here is one way to do it. I have also disabled/enabled events and added error handling. You may want to see THIS

Logic:

The first time it writes to C49, name the cell as DoNoWriteAgain. Next time simply check if the cell has been named and if it has then do not add ;)

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sName As String
    Dim rRangeCheck As Range

    On Error GoTo Whoa

    Application.EnableEvents = False

    sName = "DoNoWriteAgain"

    On Error Resume Next
    Set rRangeCheck = Range(sName)
    On Error GoTo 0

    If rRangeCheck Is Nothing Then
        If Not Intersect(Target, Range("C50")) Is Nothing Then
            If Not IsEmpty(Cells(49, 3).Value) Then
                Cells(49, 3).Value = Cells(49, 3).Value + 0.8
                ActiveWorkbook.Names.Add Name:=sName, _
                                         RefersToR1C1:="=" & ActiveSheet.Name & "!R49C3"
            End If
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Cell Value update due to change in other cell manually only once

2 Answers2