OnChange function only when a certain cell changes

[PCLIVE]

Can I do an OnChange function for just one cell? For example, anytime
A1 changes, I'd like to run some code. I don't really want to use the code
on the whole sheet if I don't have to. Can this be done?

Thanks,
Paul

 

[Chip Pearson]

Paul,

Forget about OnChange. Instead, use the Worksheet_Change event
procedure (right click on the appropriate sheet tab and choose
View Code). Something like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
' do something
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Guest]

There is no built-in event for a cell change, but the usual way to do what
you want is to test the Target range to see if it includes your cell, e.g:

Sub Worksheet_Change(ByVal Target as Range)
If Not(Intersect(Target, Range("A1")) Is Nothing) Then
' Your code goes here
End If
End Sub

The event procedure runs, but the actual code is skipped if A1 did not change.

 

[PCLIVE]

Great idea Chip!
Works great!

Thank you,
Paul

Paul,

Forget about OnChange. Instead, use the Worksheet_Change event procedure
(right click on the appropriate sheet tab and choose View Code). Something
like

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
' do something
End If
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Chip Pearson]

I'm glad it works. For more information about events, see
http://www.cpearson.com/excel/events.htm .


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kris]

Or

if not intersect (target, range("$A$1")) is nothing then ....


it works if you are intereseted in area larger than single cell i.e.

if not intersect (target, range("$A:$A")) is nothing then ....

entire A column.