Thanks. Back in my compiled VB days, I developed quite a few of these
cooperative, inter-event type procedures to solve problems which did not
have a direct coded solution available. While this one was somewhat
different from them, the underlying principle is the same... use one or more
global variables as intermediaries between event procedures and have one or
more events post a status condition of some kind and have other events check
in with the variable(s) and take action according to its contents.

--
Rick (MVP - Excel)


"JLGWhiz" wrote in message
...
That's pretty slick. I was looking for a built-in constant that would
either
detect the keystroke event or identify the change as automation, similar
to
the way it ignores calculations. I knew I had not seen anything
previously
that would discern the difference. Never even thought about using the
absence of an event as a trigger.

"Rick Rothstein" wrote:

Oops! I forgot (although I'm sure you figured it out)... alternate
between
running the macro and typing or pasting data into cells and watch the
MessageBox'es.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I was thinking along these lines. Create a Public Boolean variable in a
Module, set it to True at the start of any macros, check the variable in
the Change event and, at the end of the change event, set the variable
back
to False. Here is a very simple example...

In a Module
===========================
Public InModule As Boolean

In a Macro
===========================
Sub MyMacro()
InModule = True
Range("A1").Value = "Where did I come from?"
'
' Actual code goes here
'
End Sub

In Worksheet Change event
===========================
Private Sub Worksheet_Change(ByVal Target As Range)
If Not InModule Then
MsgBox "Cell was **NOT** changed by a macro"
'
' Actual code goes here
'
Else
MsgBox "Cell changed by a macro"
'
' Actual code goes here
'
End If
InModule = False
End Sub

--
Rick (MVP - Excel)


"JLGWhiz" wrote in message
...
Hi Rick, out of curiosity, how do you detect the keystroke so that you
can
tell that the change was not the result of code execution? Or did you
have a
different method in mind?

"Rick Rothstein" wrote:

The answer to your question is yes but, in order to give you code, we
will
need more information. Is this functionality to be confined to a
single
worksheet or do you want to track changes on any worksheet and
perform
the
Column B update on the worksheet where the change was made? Do you
want
the
update in Column B to take place automatically as soon as the cell is
changed (which would use an event procedure and not a macro) or only
when
you execute a macro manually? Are there certain columns that you want
to
track (if so, which ones) or do you want to track changes to any
column
(other than Column B, I would guess)? What is the update that you
want
to
perform in Column B?

--
Rick (MVP - Excel)


wrote in message
...
Is it possible for VB to determine the last cell that was changed.
I'm
not referring to a cell changed by a macro, just a cell that was
changed by typing into the cell. I want a macro to then update a
cell
in the same row, column B.

Thanks