A few words of thanks would be greatly appreciated.

Excel / VBA - Detecting changes in cell

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

A few words of thanks would be greatly appreciated.

Ask a question
Jean-François Pillou

CCM is a leading international tech website. Our content is written in collaboration with IT experts, under the direction of Jeff Pillou, founder of CCM.net. CCM reaches more than 50 million unique visitors per month and is available in 11 languages.

Learn more about the CCM team

Published by . Latest update on by deri58.

This document, titled "Excel / VBA - Detecting changes in cell," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).