Worksheet Change event

  • Thread starter Thread starter N1KO
  • Start date Start date
N

N1KO

I'm after a macro that'll place the date & time into a cell when another cell
is changed from No to Yes.

I need the date to then be fixed (was thinking using a Now() function but
it'd change on every re-calculate) until the cell is changed again.

The Yes & No will be cells 13-2000 in column AJ & date/time in cells 13-2000
in column AK.

Naturally the cells in each column will relate (AJ13 changes then AK13 will
display the date, etc).

Thanks
 
this is an FAQ , but anyway, add this code to the worksheets code page - to
get there , right click on the sheet tab and select View Code
we're using the CHANGE event -this fires every time a user enters new data.
We check to see if the changed cell is in the 'trigger' range - AJ13-AJ2000,
then put a time stamp in the next cell if it is....

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Set cell = Intersect(Target, Range("AJ13:AJ2000"))
If Not cell Is Nothing Then
With cell.Offset(, 1)
.Value = Now()
.NumberFormat = "dd/mm/yy HH:MM"
End With
End If
End Sub
 
This will put the date/time in AK whenever AJ changes to Yes:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToInspect As Range
Dim myIntersect As Range
Dim myCell As Range

Set RngToInspect = Me.Range("AJ13:AJ2000")
Set myIntersect = Intersect(Target, RngToInspect)

If myIntersect Is Nothing Then
Exit Sub
End If

For Each myCell In myIntersect.Cells
If LCase(myCell.Value) = LCase("yes") Then
Application.EnableEvents = False
With Me.Cells(myCell.Row, "AK")
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
.Value = Now
End With
Application.EnableEvents = True
End If
Next myCell
End Sub


It's a worksheet event. Rightclick on the worksheet tab that should have this
behavior and select view code.

Then paste this into newly opened code window.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Hi,

Right click your sheet tab, view code and paste this in and try entering yes
in your range

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("AJ13:AJ2000")) Is Nothing And _
UCase(Target) = "YES" Then
Application.EnableEvents = False
Target.Offset(, 1) = Now
Application.EnableEvents = True
End If
End Sub

Mike
 
Hi Dave,

This keeps debugging on the myIntersect bit as the variable is coming
through as nothing in the locals window and then its exiting the sub (as that
is what it's supposed to do).

Any reason why this would happen?
 
I think you changed something in the code and that broke it.

If you can't find it, it's time to post your version of the procedure.

And to make testing easier, what cell did you change and to what value?
 
Hi Dave,

Have it working now, realised i'd missed off a full stop when re-typing it
in, thanks.
 
Back
Top