Run a macro after a cell changes...

[Shaka215]

Hey,

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!!!

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A6:A10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range(WS_RANGE)) Is Nothing Then
'copy O to R
Target.Offset(0, 14).Copy
Target.Offset(0, 17).PasteSpecial
Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End If

ws_exit:
Application.EnableEvents = True
End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bz]

try Application.EnableEvents = False, as shown below.

caution: If you are debugging and turn off events, 'things' stop 'working'
'normally' until you re-enable them!

You NEED the error handler to turn events back on for similar reasons.


(e-mail address removed) wrote in @f16g2000cwb.googlegroups.com:

Hey,

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)

on error goto ex
Application.EnableEvents = False 'stop loop when change.

Range("O6").Select
Selection.Copy
Range("O38").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

ex: Application.EnableEvents = True 'reenable events

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!!!

--
bz

please pardon my infinite ignorance, the set-of-things-I-do-not-know is an
infinite set.

(e-mail address removed)