OnChange function only when a certain cell changes

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

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


"PCLIVE" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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.
--
- K Dales


"PCLIVE" wrote:

> 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
>
>
>

Great idea Chip!
Works great!

Thank you,
Paul

"Chip Pearson" <[email protected]> wrote in message
news:%[email protected]...
> 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
>
>
> "PCLIVE" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>

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


"PCLIVE" <[email protected]> wrote in message
news:[email protected]...
> Great idea Chip!
> Works great!
>
> Thank you,
> Paul
>
> "Chip Pearson" <[email protected]> wrote in message
> news:%[email protected]...
>> 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
>>
>>
>> "PCLIVE" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>
>>
>
>

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


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.