View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Richard R Richard R is offline
external usenet poster
 
Posts: 11
Default Automatic date and time updation

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" wrote:

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

Richard R wrote:

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

"Dave Peterson" wrote:

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.

Richard R wrote:

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

"Dave Peterson" wrote:

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

Qlychap wrote:

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

--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson