Run Macro on cell exit

[Kelly]

Hi group I am using the Office 2000 package

I am looking ofr a way to run a macro on the exit of a
cell.


Example I want to run the code each time the target leaves
A1


I know you can run a macro each time there is a change of
cell address that is not equal to A1 by using

"Worksheet_SelectionChange"

If Target.Address <> A1 Then

Bla Bla Bla

But I would prefer to not run this entire code each time
the cell target changes. Just when it leaves A1

One idea I had was to have part of a code run each time
the address was equal to A1 then just hold until the
address was not equal to A1 and then run. I am not sure
how to do this though.

Make sense? Other ideas?

Kelly

 

[Frank Kabel]

Hi
try something like the following:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Oldselection as range
If Intersect(Oldselection, Me.Range("A1")) is not nothing then
'run your code
end if

set oldselection = target
End Sub

 

[Frank Kabel]

Hi
sorry. Change the line
Dim Oldselection as range

to
Static Oldselection as range

 

[JE McGimpsey]

Frank -

OldSelection needs to be either declared as a global variable, or else
as a static variable.

The "Not" needs to come before Intersect - not requires an expression
that can be evaluated as boolean rather than Nothing.

 

[JE McGimpsey]

One way:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static rOldCell As Range
If Not rOldCell Is Nothing Then
If Not Intersect(rOldCell, Range("A1")) Is Nothing Then
'your code here
End If
End If
Set rOldCell = Target
End Sub

 

[Frank Kabel]

Hi JE
saw the missing Static also

the 'not' part though is a little bit embarassing (should not code
within Outlook Express...9
thanks for the correction!

 

[Kelly]

-----Original Message-----
Hi group I am using the Office 2000 package

I am looking ofr a way to run a macro on the exit of a
cell.


Example I want to run the code each time the target leaves
A1


I know you can run a macro each time there is a change of
cell address that is not equal to A1 by using

"Worksheet_SelectionChange"

If Target.Address <> A1 Then

Bla Bla Bla

But I would prefer to not run this entire code each time
the cell target changes. Just when it leaves A1

One idea I had was to have part of a code run each time
the address was equal to A1 then just hold until the
address was not equal to A1 and then run. I am not sure
how to do this though.

Make sense? Other ideas?

Kelly


.
Thanks Frank and GE

That worked but I created a new problem.

Within my code I am changeing the interior color of
several diferent ranges and each time I have a ".select"
in my code the "Worksheet_SelectionChange" code runs
again. Sort of like a circular referance I guess?

Anyway is there a way to change the color of a named range
without actually selecting the range and setting off the
Worksheet_SelectionChange code every time?

Other ideas?

Thanks

Kelly

 

[fendwick]

Hi Kelly!

How 'bout this:
- copy your procedure code into its own procedure name (e.g. 'foo')
- declare a global boolean variable (e.g. blnRun) and set you
procedure to run only if blnRun is True.
Whenever your code is ready to change a color, set blnRun to False
change your color, then reset blnRun to True again after

i.e.
Code
-------------------


:
:
blnRun = False
{change color code}
blnRun = True
:
:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
if blnRun then
call Foo(Target)
end if
End Sub

Private Sub Foo(ByVal targ as Range)
Static rOldCell As Range
If Not rOldCell Is Nothing Then
If Not Intersect(rOldCell, Range("A1")) Is Nothing Then
'your code here
End If
End If
Set rOldCell = targ
End Sub


-------------------




fendwick