Automatic date and time updation

[Qlychap]

Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep

 

[Dave Peterson]

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

 

[jindon]

Hi
Paste the code onto sheet module
1) right click on the sheet tab in question and select [ViewCode]
2) paste the code onto the blank space on the right pane
3) click x to close the window and get back to Excel

It will change the time stamp when the data actually changed

Code:
--------------------

Private OldData

Private Sub Sheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1,1)
If .Address(0,0) <> "A5" Then Exit Sub
If .Value = OldData Then Exit Sub
Application.EnableEvents = False
.Offset(1).Value = Now
Application.EnableEvents = True
OldData = .Value
End With
End Sub

 

[Guest]

I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it
made no difference. Is there a trick to using it in a protected sheet?

Richard

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

 

[Dave Peterson]

First, unprotect the worksheet.
then format the column getting the time/date the way you want.

Then protect the worksheet once again.

And remove the formatting from the code.

Changing the format of a cell on a protected sheet could be causing your
trouble.

If that doesn't work, post the code you used and indicate the line that fails.

I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it
made no difference. Is there a trick to using it in a protected sheet?

Richard

 

[Guest]

I unprotected the sheet, formatted the coulmn, inserted the code, and it
worked fine. I protected the sheet, and the code stopped running at
..NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the
sheet, not only do I not get an error, but nothing happens at all. I would
like to insert the date in mm/dd/yy format. Code follows

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Thanks
Richard

First, unprotect the worksheet.
then format the column getting the time/date the way you want.

Then protect the worksheet once again.

And remove the formatting from the code.

Changing the format of a cell on a protected sheet could be causing your
trouble.

If that doesn't work, post the code you used and indicate the line that fails.

 

[Dave Peterson]

You skipped this line:

And remove the formatting from the code.

Just delete that .numberformat line from your code.

And format that column G as mm/dd/yyyy (manually).

I unprotected the sheet, formatted the coulmn, inserted the code, and it
worked fine. I protected the sheet, and the code stopped running at
.NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the
sheet, not only do I not get an error, but nothing happens at all. I would
like to insert the date in mm/dd/yy format. Code follows

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Thanks
Richard

 

[Guest]

I wondered what you meant by removing the formatting from the code. I didn't
format the code! :<)
It still didn't work with that change, but it was in a book with 5 other
sheets. I put it into a book by itself, and it seems to work OK now. It must
have something to do with modules or some other esoteric VBA stuff. Anyway,
it works now.

Thanks greatly,
Richard

 

[Guest]

I spoke too soon. I saved the file by itself, then reopened the file, and it
now does nothing again. I viewed the code, and it is still exactly as you
suggested, but it does nothing when I change the subject cells.
--
Richard

I wondered what you meant by removing the formatting from the code. I didn't
format the code! :<)
It still didn't work with that change, but it was in a book with 5 other
sheets. I put it into a book by itself, and it seems to work OK now. It must
have something to do with modules or some other esoteric VBA stuff. Anyway,
it works now.

Thanks greatly,
Richard

 

[Dave Peterson]

You may have turned event handling off.

Close excel and reopen it and your workbook.

Or...

hit alt-f11 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = true

Then back to excel to test it out.

 

[Guest]

I did that and when I hit enter I got an error message that macros were
turned off.

 

[Dave Peterson]

Make sure your security level is not set for High (medium or low is ok).

Tools|Macro|Security|Macro Security
(medium will give you a warning prompt and MS recommends against low)

Then close your workbook and reopen it -- this setting isn't retroactive.

If you chose medium, make sure you said yes to allow macros.

I did that and when I hit enter I got an error message that macros were
turned off.

 

[Guest]

Events enabled. Security set to medium. Saved and reopened. Dialog to allow
macros "Yes". Nothing happens when I make a change.

I am tired of messing with it, and it is not absolutely necessary for it to
happen. I truely appreciate all your help and how difficult it is to fix
something by "remote control." I won't take up any more of your time. Have a
good weekend.