use vb to determine last cell changed

[jck.office]

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

 

[Mike H]

Hi,

Right click your sheet tab, view code and paste this in.

the messagebox bit isn't necessary, it's to demonstrate the method

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Last changed cell was " & Target.Address
Application.EnableEvents = False
Cells(Target.Row, 2).Value = "Something"
Application.EnableEvents = True
End Sub

Mike

 

[Rick Rothstein]

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?

 

[JLGWhiz]

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]

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

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.

 

[JLGWhiz]

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]

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.

 

[John]

Rick,

Interesting options you brought up to consider, my need is very
simple. See repsonse below.

Do you want the update in Column B to take place automatically as soon asthe cell is
changed or only when you execute a macro manually?

Not automatically, only when the macro is invoked manually.

Are there certain columns that you want to
track (if so, which ones)

No, I just need to identify the row where the last change was made.
And this is all o the same worksheet.

What is the update that you want to perform in Column B?

I have a currently working macro to enter date and some other info.
Right now I manually select the cell so I'm just looking to automate
the process to identify the correct cel to update.

 

[Rick Rothstein]

Let's use the idea I proposed. Add the following to a module...

Public InModule As Boolean

Put this procedure in the code window for the worksheet you want this
functionality on...

Private Sub Worksheet_Change(ByVal Target As Range)
If Not InModule Then
Call YourMacro(Target.Row)
End If
InModule = False
End Sub

Here I have assumed your macro is named YourMacro (change it as appropriate)
and I further assumed it will be modified to take one argument, the row
number of the cell just changed by your typing in an entry. To add the
argument to your macro (it will cease to be a macro once you do this;
instead, it will just be a plain subroutine), just create an argument for it
between the parentheses where the macro is declared. For example, if you
current macro is declared like this...

Private Sub MyMacro()
'
' Your code is located here
'
End Sub

then simple make it look like this...

Private Sub MyMacro(CurrentRow As Long)
'
' Your code is located here - wherever you now refer to the row
' for the active cell, just use the CurrentRow argument instead.
'
End Sub

--
Rick (MVP - Excel)


Rick,

Interesting options you brought up to consider, my need is very
simple. See repsonse below.

Do you want the update in Column B to take place automatically as soon as
the cell is
changed or only when you execute a macro manually?

Not automatically, only when the macro is invoked manually.

Are there certain columns that you want to
track (if so, which ones)

No, I just need to identify the row where the last change was made.
And this is all o the same worksheet.

What is the update that you want to perform in Column B?

I have a currently working macro to enter date and some other info.
Right now I manually select the cell so I'm just looking to automate
the process to identify the correct cel to update.

 

[John]

Hi,

Right click your sheet tab, view code and paste this in.

the messagebox bit isn't necessary, it's to demonstrate the method

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "Last changed cell was " & Target.Address
Application.EnableEvents = False
Cells(Target.Row, 2).Value = "Something"
Application.EnableEvents = True
End Sub

Mike






- Show quoted text -

Mike,

I've finally found some time to follow up on the suggestions in this
string (Rick, I'll try your idea soon).

For your suggestion I'm learning some more new stuff. I've never put
code on a worksheet tab so I've got to understand that better.

The message box indeed displays the cell just changed. That's cool!

So now I have this Private Sub on a sheet (code) and my original
macro1() on a module1 (code).

I think the target.row will get updated everytime new data is entered
in a cell. And your code went on to further enter something in column
B of that row, but I don't want to update cell B just yet.

I may have several cells in the row I want to change, and then when I
am done invoke the macro, which first needs to check that it is in the
right row, and then update the cell in column B. (The purpsoe of my
original request was a form of an error check. When users enter data
they can use the enter key or a tab key. In one case the active cell
is left in the wrong row.)

So, I think to use your method I need to understand how to pass the
value of the target to my macro. is this possible?

Did my description make any sense?