Calling procedure when leaving cell

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.

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


---
Regards,
Norman



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

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.


---
Regards,
Norman

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

"Norman Jones" wrote:

> 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.
>
>
> ---
> Regards,
> Norman
>
>
>

Hi Jeff,

> this works terrific although I truly don't understsand the "If not is
> nothing " statement.

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

>> Set rng = Me.Columns("K")

>> If Not Intersect(Target, rng) Is Nothing Then

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.

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?

---
Regards,
Norman



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

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..........
>>not allowing blank cell : cells L:M are possible empty but these would be allowed. I do have a column (B) where data would be mandatory for the row/record to exist so MY logic is somewhere a test like
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


"Norman Jones" wrote:

> Hi Jeff,
>
> > this works terrific although I truly don't understsand the "If not is
> > nothing " statement.
>
> The following lines are intended to limit macro action to changes in a
> column K cell:
>
> >> Set rng = Me.Columns("K")
>
> >> If Not Intersect(Target, rng) Is Nothing Then
>
> 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.
>
> 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?
>
> ---
> Regards,
> Norman
>
>
>
> "jeffP" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>