On Change code

[Patrick C. Simonds]

Is it possible to have code on a specific worksheet, say sheet1, which would
run any time the value in cell A4 (and only cell A4) were to change?

 

[OssieMac]

You could use the following On Change event. Right click on the sheet tab
name, select view code and past the code into the VBA editor. cCose the
editor by clicking on the X in the top right corner of the screen.

Unfortunately, if you change like to like (say change 5 to 5) then the code
still runs. It does not recognise whether there is a real change in the
value; only that you have edited the cell. I don't know of any simple way of
saving the old value to test whether there is a real change or only edited
with the same value. It is possible to have a copy of the worksheet if this
is essential.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$4" Then
MsgBox "Address of current cell is " & Target.Address
'Insert the required code in lieu of the msgbox.
End If

End Sub

 

[Gord Dibben]

Is the change in A4 due to a formula in A4?

Private Sub Worksheet_Calculate()
On Error GoTo stoppit
Application.EnableEvents = False
With Me.Range("A4")
If .Value <> "" Then
MsgBox "Please be advised that A1 value changed."
End If
End With
stoppit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP