I am trying to do something relatively simple using VBA but the code I wrote
is not responding. I am trying to have cell "C16" record or 'log' the date
(and the time if anyone knows how to do this) that cell "C13" is changed to
"Y". Here is my code:

Private Sub Workbook_Open()
CloseLog = Worksheets("Administrator").Cells("C13")
If CloseLog = Y Then
With Worksheets("Administrator").Cells("C16")
.Value = Date
.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
End With
End If
End Sub

Thanks in advance,
IP

If CloseLog = "Y" Then
--
Gary''s Student - gsnu200832

Use this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Set CloseLog = Worksheets("Administrator").Range("C13")
Set checkpoint = Worksheets("Administrator").Range("C16")
If Intersect(Target, CloseLog) Is Nothing Then Exit Sub
If CloseLog.Value < "Y" Then Exit Sub
If checkpoint.Value < "" Then Exit Sub
Application.EnableEvents = False
checkpoint.Value = Now
checkpoint.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200832


"iperlovsky" wrote:

I am trying to do something relatively simple using VBA but the code I wrote
is not responding. I am trying to have cell "C16" record or 'log' the date
(and the time if anyone knows how to do this) that cell "C13" is changed to
"Y". Here is my code:

Private Sub Workbook_Open()
CloseLog = Worksheets("Administrator").Cells("C13")
If CloseLog = Y Then
With Worksheets("Administrator").Cells("C16")
.Value = Date
.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
End With
End If
End Sub

Thanks in advance,
IP

If you are trying to record the date/time any time cell C13 changes to "Y",
then paste this code into that worksheet's code module (right-click on the
worksheet's name tab, select "View Code"):

Private Sub Worksheet_Change(ByVal Target As Range)
'
' This prevents recursion into this subroutine -
' set it back to True before you exit!
'
Application.EnableEvents = False
'
If (Target.Row = 13 And Target.Column = 3 And Target = "Y") Then
ActiveSheet.Cells(16, 3) = Format(Now(), "mmm dd, yyyy h:mm AMPM;@")
End If
'
Application.EnableEvents = True
End Sub

Note: set cell C16 format to "Text", note "Date" or the VBA Format
statement will not make any difference!

HTH,

Eric

That worked the first time, but then I waited 1 minute, changed the value to
"n" and the date/time stayed the same. How can we modify this code to
override the value that was returned the last time the cell was changed to
"y"?

Thanks,
IP

"Gary''s Student" wrote:

Use this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
Set CloseLog = Worksheets("Administrator").Range("C13")
Set checkpoint = Worksheets("Administrator").Range("C16")
If Intersect(Target, CloseLog) Is Nothing Then Exit Sub
If CloseLog.Value < "Y" Then Exit Sub
If checkpoint.Value < "" Then Exit Sub
Application.EnableEvents = False
checkpoint.Value = Now
checkpoint.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
Application.EnableEvents = True
End Sub

--
Gary''s Student - gsnu200832


"iperlovsky" wrote:

I am trying to do something relatively simple using VBA but the code I wrote
is not responding. I am trying to have cell "C16" record or 'log' the date
(and the time if anyone knows how to do this) that cell "C13" is changed to
"Y". Here is my code:

Private Sub Workbook_Open()
CloseLog = Worksheets("Administrator").Cells("C13")
If CloseLog = Y Then
With Worksheets("Administrator").Cells("C16")
.Value = Date
.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
End With
End If
End Sub

Thanks in advance,
IP

It appears that you are using more code than is necessary anyway. Unless you
require the CloseLog variable elsewhere, it is not needed here, you are
setting the variable to the contents of a cell, and then testing the
variable. Also, you are using Date and then formtatting the cell to try to
show the time.

Try:

If Worksheets("Administrator").Cells("C13") = "Y" Then
Worksheets("Administrator").Cells("C16") = Now
End If

I would also suggest that your code isn't going to log the date/time that
your chosen cell changes to Y, what it will do is log the date/time that the
workbook is opened following that cell being changed to Y. If you want to log
the change then you can either perform this check using the Workbook_Close
event, or use one of the Worksheet events to test your conditions. You should
also be careful, as whatever event you use, the next time that event happens
the date/time will be overwritten. So if I change the cell to Y and close the
workbook, that date/time is logged. If that book is opened again, nothing
changed, and then closed, the log will be rewritten. Perhaps you could append
the cell:

Worksheets("Administrator").Cells("C16") = _
Worksheets("Administrator").Cells("C16") & ", " & Now

or something like that?

Cheers,

Sean.


--
(please remember to click yes if replies you receive are helpful to you)


"iperlovsky" wrote:

I am trying to do something relatively simple using VBA but the code I wrote
is not responding. I am trying to have cell "C16" record or 'log' the date
(and the time if anyone knows how to do this) that cell "C13" is changed to
"Y". Here is my code:

Private Sub Workbook_Open()
CloseLog = Worksheets("Administrator").Cells("C13")
If CloseLog = Y Then
With Worksheets("Administrator").Cells("C16")
.Value = Date
.NumberFormat = "mm/dd/yyyy h:mm AM/PM"
End With
End If
End Sub

Thanks in advance,
IP

Purrrrrfect! Thanks.
IP

"egun" wrote:

If you are trying to record the date/time any time cell C13 changes to "Y",
then paste this code into that worksheet's code module (right-click on the
worksheet's name tab, select "View Code"):

Private Sub Worksheet_Change(ByVal Target As Range)
'
' This prevents recursion into this subroutine -
' set it back to True before you exit!
'
Application.EnableEvents = False
'
If (Target.Row = 13 And Target.Column = 3 And Target = "Y") Then
ActiveSheet.Cells(16, 3) = Format(Now(), "mmm dd, yyyy h:mm AMPM;@")
End If
'
Application.EnableEvents = True
End Sub

Note: set cell C16 format to "Text", note "Date" or the VBA Format
statement will not make any difference!

HTH,

Eric