Calling procedure when leaving cell

J

jeffP

Hi all,
I have a datasheet that I’ve written some code to find the first empty row.
I will then enter data across that row to Column M. I will enter some data
in Column K that I want to check by running some IF statements when I leave
the current cell in Column K. What I don’t know how to do is how to call that
routine when I leave the current cell in Column K.
Is it possible?
Any help or suggestions are always appreciated.
 
N

Norman Jones

Hi Jeff,

Try:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

If Target.Count > 1 Then Exit Sub

Set rng = Me.Columns("K")

If Not Intersect(Target, rng) Is Nothing Then
'Your processing code
End If

End Sub
'<<=============
 
N

Norman Jones

Hi Jeff,

The suggested code represents an event procedure and should be pasted into
the worksheet code module - not a standard module.

If you want to run an existing procedure when a column K cell is changed,
add a line like:

Call MyMacro

as your processing code.
 
J

jeffP

Norman,
this works terrific although I truly don't understsand the "If not is
nothing " statement. I'll keep trying to understand so someday...I can write
them myself.
In the meantime, I still need help. When this runs I call some nested IF's
to check and proceed on different answers. My problem is the the Work_Sheet
change is just that: if the cell is left empty (which I don't want to allow)
no change happens to call the trailing code. Can I somehow check for a blank
cell, with this or some other way.

As always any help and education is greatly appreciated
 
N

Norman Jones

Hi Jeff,
this works terrific although I truly don't understsand the "If not is
nothing " statement.
Click to expand...

The following lines are intended to limit macro action to changes in a
column K cell:

I would have liked to say:

If Intersect(Target, rng) Is Something Then

but the keyword something does not exist. Since, however, the keyword
Nothing does exist, I use this with a double negative to obtain a valid
equivalent expression.
My problem is the the Work_Sheet change is just that: if the cell is
left empty (which I don't want to allow) no change happens to call
the trailing code. Can I somehow check for a blank cell, with this
or some other way.
Click to expand...

As written, the code is invoked in response to a change in a column K cell:
if an entry is deleted (so that the cell becomes blank), the code will run.

Would your purpose be satisfied if code were to check the column K cell when
an entry is made in the corresponding column L:M cells?
 
J

jeffP

Norman,
Thanks for the explaination/education. At least on the right track. I was
trying to "simplify" the code by eliminating the double negatives so MY logic
:
If Intersect(target,rng) then
but alas.......... :)If B5 >"" Then
but I don't know if this is a proper test or where to put it. As you know MY
logic and VBA logic is not necessarily compatible :)
thanks again
jeffp
 
Top