CODEWITHSUNDEEP
excelvba
JMB
JMB

Reputation: 29

Excel Worksheet_Change not detecting changes

Unfortunately Worksheet_change is not working for me. I am using a Sheet where the first column is NOW() function. If I click anywhere in the excel the time on this cell changes, but the Worksheet_Change just don't detect it.

A2 is using =NOW()

Anyone knows how to fix this issue? I have tried several different ways and no one works.

'Option Explicit
'Option Base 1
Dim xVal As Double
'Update by Extendoffice 2018/8/22
'Private Sub Worksheet_Calculate(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Address = Range("$A$2").Address Then
        'Rows(3).Insert Shift:=xlDown
        'Range("$A$3").Value = Now
        'Range("$B$3").Value = xVal
        'Range("$C$3").Value = Range("$C$2").Value
    'Else
        If xVal <> Range("$B$2").Value Then
            Debug.Print xVal & " <- xVal IF"
            Debug.Print Range("B2").Text & "<- Text IF"
            Rows(3).Insert Shift:=xlDown
            Range("$A$3").Value = Now
            Range("$B$3").Value = xVal
            Range("$C$3").Value = Range("$C$2").Value
        End If
    End If
    Application.EnableEvents = True
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    xVal = Range("$B$2").Value
End Sub

Upvotes: 0

Views: 754

Answers (1)

VBasic2008
VBasic2008

Reputation: 54767

A Worksheet_Calculate Event Study

Google Drive

Cell A2 in worksheet Sheet1 contains the formula =B2.

Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    WsCalc
End Sub

' Only to trigger the calculate event when different cell is selected.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ThisWorkbook.Worksheets("Sheet1").Range("B2") = Int(Rnd() * 2 + 1)
End Sub

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    WsCalc
End Sub

Module1

Option Explicit

Public lngSource As Long ' Current Source Value

Sub WsCalc()

    Dim rngSource As Range        ' Source Cell Range

    ' Create a reference to Source Cell Range (rngSource).
    Set rngSource = ThisWorkbook.Worksheets("Sheet1").Range("A2")

    ' When the workbook opens, Current Source Valuec (lngSource) is equal to "".
    ' Therefore:
    If lngSource = 0 Then
        ' Initialize Current Source Value (lngSource) i.e. write value
        ' of Source Cell Range (rngSource) to Current Source Value (lngSource).
        lngSource = rngSource.Value
        MsgBox "Monitoring started (lngSource=" & lngSource & ")."
        Exit Sub
    End If

     ' If you need disabling events, this is how you implement it. Not needed
     ' in this code.
'    Application.EnableEvents = False
        On Error GoTo ProgramError
        ' Check value of Source Cell Range (rngSource)
        ' against Current Source Value (lngSource).
        If rngSource.Value <> lngSource Then
            ' The value has changed.
            MsgBox "The value has changed from '" & lngSource & "' to '" _
              & rngSource.Value & "'."
            lngSource = rngSource.Value
        Else
            ' The value hasn't changed (usually no code).
            MsgBox "Value NOT changed, still '" & lngSource & "'"
        End If

SafeExit:
'    MsgBox "Enabling events before exiting."
'    Application.EnableEvents = True
Exit Sub

ProgramError:
    ' Improve this error handling.
    MsgBox "An unexpected error occurred."
    On Error GoTo 0
    GoTo SafeExit

End Sub

Upvotes: 1

Related Questions