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


"N1KO" wrote in message
...
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 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 he
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.)

N1KO wrote:

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

--

Dave Peterson

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?

"Dave Peterson" wrote:

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 he
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.)

N1KO wrote:

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

--

Dave Peterson

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?

N1KO wrote:

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?

"Dave Peterson" wrote:

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 he
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.)

N1KO wrote:

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

--

Dave Peterson


--

Dave Peterson

Hi Dave,

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

"Dave Peterson" wrote:

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?

N1KO wrote:

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?

"Dave Peterson" wrote:

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 he
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.)

N1KO wrote:

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

--

Dave Peterson


--

Dave Peterson

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

"N1KO" wrote:

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