Time Stamp help

  • Thread starter Thread starter Gerard Sanchez
  • Start date Start date
G

Gerard Sanchez

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

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