Excel / VBA - Detecting changes in cell

October 2016

When using the VBA, detecting the changes in cells is very easy. It can be done through the Event Change feature on the Excel worksheet. However, there is no method to determine the contents of the cell with the Event Change feature. Also, the event change just takes the active cell into consideration and it cannot check for changes once the cell is left. Although the cell is no longer selected, It is not difficult to detect the changed cell content with the VBA. It is simple to manage a VBA event for a cell change. With the VBA, detecting the changes in the cell adds flexibility and features to Office software.

The Event Change feature of a sheet detects changes in the active cell but it gives no information about the content. The example given below will help you find out if a cell still works after the contents have been modified. Copy and paste the code, adding in the action you want to be taken when a cell is changed.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)           
Static AncAdress As String, AncCell As Variant           
    If AncAdress <> "" Then 'for first initialization.           
        If AncCell <> Range(AncAdress) Then           
            'The cell that you just left has been changed.            
            'Put action to be taken.           
        End If           
    End If           
    AncAdress = Target.Address           
    AncCell = Target.Value2           
End Sub

Related :

This document entitled « Excel / VBA - Detecting changes in cell » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.