Can VB determine location of last cell changed?

[John]

I tried posting this on Saturday and I think it got lost in the
problem google was having since I don't see it now that the log jam
appears to have been broken. My apologies if this is a duplicat post.

Is it possible for VB to determine what was the last cell that was
changed, that is the last cell that had data typed into it not by a
macro? Excel must know where this cell is located in order to execute
the undo command, I just want to know if VB can dtermine the location
of this cell. I want to be able to change a cell in the same row as
this last changed cell.

Thanks

 

[Barb Reinhardt]

Do you need something in addition to the responses here?

http://www.microsoft.com/office/com...&p=1&tid=f2636783-0d3e-4ce5-868b-c4560e78778a

Barb Reinhardt

 

[John]

Do you need something in addition to the responses here?

http://www.microsoft.com/office/community/en-us/default.mspx?&query=J....

Barb,

Link above is missing post that I referred to in original post in this
string.

Items are finally beginning to appear in google groups and I now can
see my post from Saturday and the replies there. I'm now waiting for
Rick's next post in response to the answers from his questions about
my post.

 

[Rick Rothstein]

Items are finally beginning to appear in google groups and I

now can see my post from Saturday and the replies there.
I'm now waiting for Rick's next post in response to the
answers from his questions about my post.

Not sure why things are going so slowly in the other thread, but since you
can see the code I originally posted there, here is the answer I posted back
to you (in case it shows up quicker for you in this thread than in the
original thread)...

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

 

[John]

Not sure why things are going so slowly in the other thread, but since you
can see the code I originally posted there, here is the answer I posted back
to you (in case it shows up quicker for you in this thread than in the
original thread)...

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 forit
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,

Thank you for your suggestion, which I think will do what I am trying
to accomplish.

But I need to learn a little more to implement and try your
suggestion.

My macro is currently in what looks to be labeled Module 1 (Code).

When you say:

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

Public InModule As Boolean

What exactly do I need to do? Do I need to create a different module?

Also, the first line in my macro is "Sub Macro1()". What is the
significance of the first line being "Private Sub MyMacro(CurrentRow
As Long)" I understand passing the parameter, I'm referring to the
term "PRivate".

John (still learning)

 

[Rick Rothstein]

You can put this line...

Public InModule As Boolean

in the current module you now have (it will work fine from any module, so
your current one is okay to use). The Private part was an error on my
part... leave it off or use Public as the declaration type.

--
Rick (MVP - Excel)

Not sure why things are going so slowly in the other thread, but since you
can see the code I originally posted there, here is the answer I posted
back
to you (in case it shows up quicker for you in this thread than in the
original thread)...

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,

Thank you for your suggestion, which I think will do what I am trying
to accomplish.

But I need to learn a little more to implement and try your
suggestion.

My macro is currently in what looks to be labeled Module 1 (Code).

When you say:

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

Public InModule As Boolean

What exactly do I need to do? Do I need to create a different module?

Also, the first line in my macro is "Sub Macro1()". What is the
significance of the first line being "Private Sub MyMacro(CurrentRow
As Long)" I understand passing the parameter, I'm referring to the
term "PRivate".

John (still learning)