Event Sub when one specific cell changes

  • Thread starter Thread starter Paul Kraemer
  • Start date Start date
P

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
 
As ALWAYS, post your code for comments.

if not intersect(target,range("a1"))is nothing then
 
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.
 
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]
 
Back
Top