Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Last time modified timestamp
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default Last time modified timestamp
I have code that enters date and time in column E if 100 % (1) is entered in
column D, how do i modify this code to have 1 more timestamp in column F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add "last time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Last time modified timestamp
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is entered
in
column D, how do i modify this code to have 1 more timestamp in column F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add "last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default Last time modified timestamp
Yes but i wanted last modified column to respond to ANY changes in column B
not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is entered
in
column D, how do i modify this code to have 1 more timestamp in column F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add "last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 11,272
Default Last time modified timestamp
So remove that test

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Yes but i wanted last modified column to respond to ANY changes in column
B
not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is
entered
in
column D, how do i modify this code to have 1 more timestamp in column
F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add
"last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 33
Default Last time modified timestamp
I get following error when this macro triggers:

Compile error
End With without With

"Bob Phillips" wrote:

So remove that test

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
Yes but i wanted last modified column to respond to ANY changes in column
B
not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...
I have code that enters date and time in column E if 100 % (1) is
entered
in
column D, how do i modify this code to have 1 more timestamp in column
F
which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add
"last
time
modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub






Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 88
Default Last time modified timestamp
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If 'added line
End With

errHandler:
Application.EnableEvents = True

End Sub

Regards
Rowan

Mikus wrote:
I get following error when this macro triggers:

Compile error
End With without With

"Bob Phillips" wrote:


So remove that test

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...

Yes but i wanted last modified column to respond to ANY changes in column

B

not only if i enter 1 (100%) but if i enter any value

"Bob Phillips" wrote:


Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHandler:
Application.EnableEvents = False

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Not Intersect(.Cells, Me.Range("D:D")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
ElseIf Not Intersect(.Cells, Me.Range("B:B")) Is Nothing Then
If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
With .Offset(0, 4)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Mikus" wrote in message
...

I have code that enters date and time in column E if 100 % (1) is

entered

in

column D, how do i modify this code to have 1 more timestamp in column

F

which would be entered each time i change value in column B?

What i have now is - "task complete timestamp", now i want to add

"last

time

modified timestamp"

This is the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

With Target
If .Cells.Count 1 Then Exit Sub 'one cell at a time
If Intersect(.Cells, Me.Range("D:D")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

If IsNumeric(.Value) Then
'.Style = "Percent"
If .Value = 1 Then
Application.EnableEvents = False
With .Offset(0, 1)
.Value = Now
.NumberFormat = "mm/dd/yyyy hh:mm:ss"
End With
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Query and Time Portion of Timestamp SPMRYT Excel Discussion (Misc queries) 0 July 30th 08 02:39 AM
"last modified" timestamp function in excel 2003 veek New Users to Excel 6 July 26th 08 05:13 AM
Formating a timestamp as a date and time. Dan Excel Worksheet Functions 3 April 20th 07 05:38 PM
Converting UTC timestamp value to date and time SK Excel Discussion (Misc queries) 1 March 19th 07 02:40 AM
Enter timestamp when column is modified Mikus Excel Programming 5 September 4th 05 05:38 AM


All times are GMT +1. The time now is 05:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement