CELL CHANGE EVENT

[Guest]

Hello. I'm trying to figure out how to run a function when data in a
particular cell changes. What's the change handler I would use for that?
Thanks.

 

[Oliver Ferns via OfficeKB.com]

Hi,
it depends how the cell value is changed. if it is by user interaction then
the Worksheet_Change event will suffice..

Private Sub Worksheet_Change(ByVal Target As Range)
If target.Address = "$A$1" then
....your code

End If
End Sub

However, there are certain things that do not fire this event. For example
when a linked cell from a Forms Listbox changes this does not fire this
event...

Hth,
Oli

 

[Guest]

Hi Dan,

you can write a macro inside a sub for the sheet in question using:

Private Sub Worksheet_Change(ByVal Target As Range)

....macro to run when there is a change....

End Sub

Note: this will run regardless of which cell changes, so you may want to run
a check against the cell that is changing to see if the macro should be run.

Simon

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)