Hi,

Currently I we have this VBA code that puts in the date and time
of last edit of the worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

End Sub


I am wonder if anyone can revise this so that when current Date
is returned the code dies; any changes made subsequently to the
worksheet the following day wouldn't change its value??

Gerard

Make your first line something like this

if ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss") then exit sub


"Gerard Sanchez" wrote:

Hi,

Currently I we have this VBA code that puts in the date and time
of last edit of the worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

End Sub


I am wonder if anyone can revise this so that when current Date
is returned the code dies; any changes made subsequently to the
worksheet the following day wouldn't change its value??



.

I usspect your TARGET is a date with hours minutes and seconds. To convert a
date/time number to just a date you can use the INT() function to remove the
fractional part of the number which is the hours/minutes/seconds which leaves
just the date.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

'remove hours minutes and seconds from targetand compare with date
if Int(target) < date()
ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")
end if
End Sub



"Gerard Sanchez" wrote:

Hi,

Currently I we have this VBA code that puts in the date and time
of last edit of the worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

End Sub


I am wonder if anyone can revise this so that when current Date
is returned the code dies; any changes made subsequently to the
worksheet the following day wouldn't change its value??



.

'This code automatically names the worksheet as dates plus makes a ding
sound whenever a value in keypuch on specified cells.
'I had previously omitted some of the code (ding part) thinking that it
wasn't relevant to the question.

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss") Then Exit Sub
does not work because there are other code that follows.

How do I make it so that after the DATE value is returned, the "date part of
the code" dies.

Here's the code:

__________________________

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

Set TargetRange = Range("H50:H51,H102:H103,H154:H155,H206:H207, _
H258:H259,H310:H311,H362:H363,H414:H415,H466:H467, H518:H519,_
H570:H571,H622:H623,H674:H675,H726:H727,H778:H779, H830:H831 ")

Set isect = Intersect(Target, TargetRange)

If Not isect Is Nothing Then
If isect.Count 1 Then Exit Sub
If Target.Row Mod 2 = 0 Then
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
Else
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(-1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
End If
End If

End Sub

___________________________________
"Gerard Sanchez" wrote in message
...
Hi,

Currently I we have this VBA code that puts in the date and time
of last edit of the worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

End Sub


I am wonder if anyone can revise this so that when current Date
is returned the code dies; any changes made subsequently to the
worksheet the following day wouldn't change its value??

Gerard

Add a couple of lines
If ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss") Then lDoIt = false
else lDoIt = True

if lDoIt = true
' Do your stuff when the sheet already exists
else
' Create your new sheet
endif

' Add any code that you run every time


"Gerard Sanchez" wrote:

'This code automatically names the worksheet as dates plus makes a ding
sound whenever a value in keypuch on specified cells.
'I had previously omitted some of the code (ding part) thinking that it
wasn't relevant to the question.

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss") Then Exit Sub
does not work because there are other code that follows.

How do I make it so that after the DATE value is returned, the "date part of
the code" dies.

Here's the code:

__________________________

Private Declare Function sndPlaySound32 Lib "winmm.dll" Alias _
"sndPlaySoundA" (ByVal lpszSoundName As String, _
ByVal uFlags As Long) As Long


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

Set TargetRange = Range("H50:H51,H102:H103,H154:H155,H206:H207, _
H258:H259,H310:H311,H362:H363,H414:H415,H466:H467, H518:H519,_
H570:H571,H622:H623,H674:H675,H726:H727,H778:H779, H830:H831 ")

Set isect = Intersect(Target, TargetRange)

If Not isect Is Nothing Then
If isect.Count 1 Then Exit Sub
If Target.Row Mod 2 = 0 Then
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
Else
If Target.Value = "" Or Target.Value = 0 Then Exit Sub
If Target.Value = Target.Offset(-1, 0).Value Then
sndPlaySound32 "ding", 1&
End If
End If
End If

End Sub

___________________________________
"Gerard Sanchez" wrote in message
...
Hi,

Currently I we have this VBA code that puts in the date and time
of last edit of the worksheet:


Private Sub Worksheet_Change(ByVal Target As Range)

Dim dateTemp As Date

ActiveSheet.Names.Add Name:="_timestamp", RefersTo:=Now()
dateTemp = Val(Mid(ActiveSheet.Names("_timestamp"), 2))

ActiveSheet.Name = Format(dateTemp, "MMM dd.hh.mm.ss")

End Sub


I am wonder if anyone can revise this so that when current Date
is returned the code dies; any changes made subsequently to the
worksheet the following day wouldn't change its value??





.