Automatic date and time updation

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

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

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

Please Login or Register  to view this content.

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
>

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

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
>

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

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
>

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


"Richard R" wrote:

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

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.

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

--

Dave Peterson

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


"Dave Peterson" wrote:

> 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.
>
> Richard R wrote:
> >
> > 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
> >
> > "Richard R" wrote:
> >
> > > 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
> > > >
>
> --
>
> 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.


Richard R wrote:
>
> I did that and when I hit enter I got an error message that macros were
> turned off.
> --
> Richard
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > Richard R wrote:
> > >
> > > 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
> > >
> > > "Richard R" wrote:
> > >
> > > > 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
> > > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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


"Dave Peterson" wrote:

> 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.
>
>
> Richard R wrote:
> >
> > I did that and when I hit enter I got an error message that macros were
> > turned off.
> > --
> > Richard
> >
> > "Dave Peterson" wrote:
> >
> > > 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.
> > >
> > > Richard R wrote:
> > > >
> > > > 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
> > > >
> > > > "Richard R" wrote:
> > > >
> > > > > 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
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>