Excel cell value change event?

[sjh]

Hi there,

Is there a value change event for a Cell in Excel worksheet? I know that they exist for form controls such as text box, but was wondering if it was possible to capture the event when user changes a value in a Cell.

Thank you & have a nice day,

 

[DaleWatson123321]

sjh,

One option would be to use a Workbook_Change event such as...

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveCell.Address = [inp1].Address Then
Change_Inp1
End If
End Sub

...where "inp1" is a range name assigned to the specific cell you refer to regarding a change.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

Dale's solution is not complete. It works fine if the user specifically enters something into the specified cell, but if the user changes the contents by copying a range into a range that happens to include the specified cell (but the specified cell is not the top-left cell of the target range), the cell contents will change and the macro will not be executed.

Here is a slightly different version that handles that case:
[blue]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Not Intersect(Target, Range("inp1")) Is Nothing Then
    Change_Inp1
  End If
End Sub

[/color]
Of course, neither version caters to the possibility that the user simply re-keyed (or re-copied) the same value in the specified cell without actually changing it. You might want to add code to store the value in a global variable and test against that to determine whether the contents actually changed or not and run or not run the macro accordingly.