Run a macro after a particular cell changes...

[Shaka215]

Hey,

I posted a question yesterday but I think I wasn't very clear about
what it was I was looking for my macro to do. I have a series of entry
feilds that I want to attach a time to. The situation is the
following...

I have range D7 with information entered in by the end user.
I want range D7 to copied and pasted into range N38.
In range O38 I want the current time to be placed next to it.

The same repeats for the other five feilds, except for this time when
someone goes to D8 it will move to the next available spot in the copy
destionation which would be P38 and then have the time to be inserted
into Q38.

So how can I tell the spreadsheet to launch a specific macro after a
specific feild is changed? I am figuring if I can get a macro to do one
thing at a time I can achive this result but I am not sure how to
specifically tell Excel...

"After cell D7 changes run macro 'copytodbstring1' "
"After cell D8 changes run macro 'copytodbstring2' "
"After cell D9 changes run macro 'copytodbstring3' "
"After cell D10 changes run macro 'copytodbstring4' "
"After cell D11 changes run macro 'copytodbstring5' "


==== ORIGINAL MESSAGE ====
I have a spreadsheet that I need a macro to exectue after a
particular valued is changed. I have 5 entry feilds I am looking to
attach a time to once it is entered into a spreadsheet. I want it so
that the user enters a value and then the time is inserted next to the
value after it is entered. I have tried the following code but the
problem is that as soon as the time is posted it loops the macro and
then it launches after each time any of the cells is changed on the
macro runs.


Private Sub Worksheet_Change(ByVal Target As Range)
Range("O6").Select
Selection.Copy
Range("O38").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End Sub


I want it so say someone enters a value on A6 it takes the value of A6
and pastes it to T6 and then the time is inserted into T7...the macro
stops. The next time the end users goes to A7 and enters a value it
goes to T8 and then the new time goes to T9. Please help!!!

 

[Tom Ogilvy]

Right click on the worksheet tab and select view code

At the top of the resulting module:
from the left dropdown select Worksheet
From the right dropdown select Change

this should put in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub


at code to this

Private Sub Worksheet_Change(ByVal Target As Range)
On Error goto ErrHandler
if Target.count > 1 then exit sub
if Target.column = 4 and Target.row > 7 and Target.row < 12 Then
if isemtpy( Range("N38")) then
set rng = Range("N38")
else
set rng = Cells(38,256).End(xltoLeft)
end if
Application.EnableEvents = False
rng.Value = Target.value
rng.offset(0,1).Value = Time
rng.offset(0,1).Numberformat = "hh:mm:ss"
rng.offset(0,1).EntireColumn.Autofit
end if
ErrHandler:
Application.EnableEvents = True
End Sub