Event Sub when one specific cell changes

[Paul Kraemer]

Hi,

I am using Excel 2007. I have written a VBA Sub that I want to be called
any time the value in one particular cell is changed.

I know I can do this by creating a Worksheet_Change() Sub and checking the
'Target' Range parameter to see if the changed cell is the one I want to
watch. The drawback of this is that this Sub gets called any time *any* cell
is changed. In my case, this will get called alot because I have a value in
another cell that changes once per second (it displays the current time).

I was wondering if there is an event Sub other than Worksheet_Change() that
would be called only when the one cell I want to watch changes?

Thanks in advance,
Paul Kraemer

 

[Don Guillett]

As ALWAYS, post your code for comments.

if not intersect(target,range("a1"))is nothing then

 

[Phil Hibbs]

Paul asked "I know how to do X, but can I do Y?", and you answered
"Here's how to do X".

Having said that, I'm pretty sure the answer is no, you have to just
check in the Worksheet_Change sub.

Phil Hibbs.

 

[Chip Pearson]

There is not an event that would function like Celll_Change. It would
be nice, but it isn't so. It is possible (in theory) to use a set of
classes to handle the Worksheet events for only specific cells, but
that wouldn't buy much, either in performance or simplicity.
Basically, you're stuck with Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case LCase(Target.Address(False, False))
Case "a1"
Debug.Print "do something for A1"
Case "c3"
Debug.Print "do something for C3"
' and so on for each cell in question
End Select
End Sub

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]