Thanks for sharing your "Excell Smarts"

The problem is:

From a user selected cell, I need to copy the line, and paste it in a
specified cell, modify the line, and return to the cell that was first
selected, and paste the new "altered" line where it was when I started.
Excell needs to remember where I first selected the cell cause this is
quite a large spreadsheet. Is there a way to store and retrieve a cell
address to use with the copy and delete functions. Oh, this is all in
Visual Basic programed macros.

I just have to keep remembering that all who wander are not lost.

Thanks...
"The wander"


--
spydor
------------------------------------------------------------------------
spydor's Profile: http://www.excelforum.com/member.php...o&userid=28438
View this thread: http://www.excelforum.com/showthread...hreadid=496842

You should use a worksheet change event. You probably do not need to move
the value to the 'specified' cell you mention, just manipulate user's entry
in VBA. A worksheet change event can watch for the user's entry and handle
what you are trying to do.

Here's a post from earlier this year by Dave Peterson (one of the most
knowledgable posters) discussing the subject

There are event macros that just wait looking for certain things to happen.

Each worksheet has a worksheet_change event that is always running waiting for
you to do something. You can tie into that event if you want.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

Here's an example that will look for a typing change in A1 and modify B1
when A1
changes.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End With

errHandler:
Application.EnableEvents = True

End Sub

Target is the range that is changed.

The program will look to see how many cells changed. If that count is larger
than 1, then it gets the heck out.

It looks to see if you changed A1, if it's some other cell, it gets out.

If there's an error, the code jumps to the error handler (near the bottom).
It
enables events (so xl can continue monitoring for more changes).

The next line (application.enableevents =false) tells excel to stop looking
for
changes. We don't want our update to B1 to cause the event to fire again (and
again and again...).

..offset(0,1) is from a starting point of Target (which is A1 in this example).
So one column to the right of A1 (same row) is B1.

It changes the value in B1 to the date/time (now) and formats the cell to show
the date/time.

Then it continues through the error handler and tells excel to start looking
for
changes again.

If you want to test this, start a new workbook.
Rightclick on the worksheet tab that should have this behavior. Select View
Code and paste this into the code window.

Then back to excel and change a few cells -- then change A1!

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm



"spydor" wrote:


Thanks for sharing your "Excell Smarts"

The problem is:

From a user selected cell, I need to copy the line, and paste it in a
specified cell, modify the line, and return to the cell that was first
selected, and paste the new "altered" line where it was when I started.
Excell needs to remember where I first selected the cell cause this is
quite a large spreadsheet. Is there a way to store and retrieve a cell
address to use with the copy and delete functions. Oh, this is all in
Visual Basic programed macros.

I just have to keep remembering that all who wander are not lost.

Thanks...
"The wander"


--
spydor
------------------------------------------------------------------------
spydor's Profile: http://www.excelforum.com/member.php...o&userid=28438
View this thread: http://www.excelforum.com/showthread...hreadid=496842