Tom Ogilvy Wrote:[color=blue]
All three times Change fires, the value of the cell is the same.
Couldn't
you add code to update the value in the temp sheet, so the 2nd and 3r
time
it fires, it would not be seen as a change?
--
Regards,
Tom Ogilvy

"timconstan" wrote i
message
...

I appreciate the reply, but that's not it...

The worksheet_change event is NOT going off until the user enters a
valid time, and then it is going off twice if the user first entere
an
invalid time, but then corrected their entry with a valid time.


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EndMacro
Application.EnableEvents = False

Dim lsChangeTo As String
Dim llRow As Long
Dim llDataRow As Long
Dim llDataColumn As Long
Dim ldDate As Date
Dim llTarget As Range
Dim ltText As String

Set llTarget = Target

If TypeName(Selection) < "Range" Then Exit Sub

Sheets("Timesheet").Unprotect

For Each cell In Target
llDataColumn = cell.Column
llDataRow = cell.Row

If cell.EntireRow.Hidden Then
cell.EntireRow.Hidden = False
End If

If llDataColumn = "2" And llDataColumn <= "5" Then
If (llDataRow = 4 And llDataRow <= 17) Or (llDataRow = 28 And
llDataRow <= 41) Then

' Cycle through the range

' See if value really changed
If Sheets("Temp").Cells(llDataRow, llDataColumn).Value <
cell.Value
Then

' Get the row to load the data
llRow = Sheets("Revisions").Range("A65536").End(xlUp).Offs et(1
0).Row

' Set the date that was changed
ldDate = Range(Cells(llDataRow, 1), Cells(llDataRow, 1)).Value
Sheets("Revisions").Cells(llRow, 1).Value = Format(ldDate
"mm/dd/yyyy")

' Set the column that was changed
If llDataRow Mod 2 = 0 Then
ltText = "First Row "
Else
ltText = "Second Row "
End If
If llDataColumn Mod 2 = 0 Then
ltText = ltText & "Time In"
Else
ltText = ltText & "Time Out"
End If

Sheets("Revisions").Cells(llRow, 2).Value = ltText

' Set was
Sheets("Revisions").Cells(llRow, 3).Value =
Sheets("Temp").Cells(llDataRow, llDataColumn).Value

' Set now
Sheets("Revisions").Cells(llRow, 4).Value = cell.Value

' Set changed
Sheets("Revisions").Cells(llRow, 5).Value = Format(Now()
"m/d/yyyy
h:mm:ss AM/PM")

' Set changed by
Sheets("Revisions").Cells(llRow, 6).Value = fOSUserName()

End If

End If
End If
Next cell

ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

EndMacro:
Application.EnableEvents = True
End Sub
--------------------


Well, I didn't want to... but I guess there's no way around it.

Here's the revised code:


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo EndMacro
Application.EnableEvents = False

Dim lsChangeTo As String
Dim llRow As Long
Dim llDataRow As Long
Dim llDataColumn As Long
Dim ldDate As Date
Dim llTarget As Range
Dim ltText As String
Dim ldtWas As Date

Set llTarget = Target

If TypeName(Selection) < "Range" Then Exit Sub

Sheets("Timesheet").Unprotect

For Each cell In Target
llDataColumn = cell.Column
llDataRow = cell.Row

If cell.EntireRow.Hidden Then
cell.EntireRow.Hidden = False
End If

If llDataColumn = "2" And llDataColumn <= "5" Then
If (llDataRow = 4 And llDataRow <= 17) Or (llDataRow = 28 And llDataRow <= 41) Then

' See if valid
' cell.Value = Format(cell.Value, "h:mm AM/PM")
' MsgBox Format(cell.Value, "h:mm AM/PM")

' a1 to c5 = Range(Cells(1, 1), Cells(5, 3))
' Cycle through the range

' If Sheets("Temp").Cells(llDataRow, llDataColumn).Value < "" Then

' See if value really changed
If Sheets("Temp").Cells(llDataRow, llDataColumn).Value < cell.Value Then

' Get the row to load the data
llRow = Sheets("Revisions").Range("A65536").End(xlUp).Offs et(1, 0).Row

' If date that was changed, column that was changed, & "was" is all the same as previous row,
' then there was really no change

' get the date that was changed
ldDate = Range(Cells(llDataRow, 1), Cells(llDataRow, 1)).Value

' get the column that was changed
If llDataRow Mod 2 = 0 Then
ltText = "First Row "
Else
ltText = "Second Row "
End If
If llDataColumn Mod 2 = 0 Then
ltText = ltText & "Time In"
Else
ltText = ltText & "Time Out"
End If

' Get the "was value"
ldtWas = Sheets("Temp").Cells(llDataRow, llDataColumn).Value

' See if values = previous values

If (Sheets("Revisions").Cells(llRow - 1, 1).Value < ldDate) Or _
(Sheets("Revisions").Cells(llRow - 1, 2).Value < ltText) Or _
(Sheets("Revisions").Cells(llRow - 1, 3).Value < ldtWas) Then

' Set the date that was changed
Sheets("Revisions").Cells(llRow, 1).Value = Format(ldDate, "mm/dd/yyyy")

' Set the column that was changed
Sheets("Revisions").Cells(llRow, 2).Value = ltText

' Set was
Sheets("Revisions").Cells(llRow, 3).Value = Sheets("Temp").Cells(llDataRow, llDataColumn).Value

' Set now
Sheets("Revisions").Cells(llRow, 4).Value = cell.Value

' Set changed
Sheets("Revisions").Cells(llRow, 5).Value = Format(Now(), "m/d/yyyy h:mm:ss AM/PM")

' Set changed by
Sheets("Revisions").Cells(llRow, 6).Value = fOSUserName()

End If

End If

End If
End If
Next cell

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells

EndMacro:
Application.EnableEvents = True
End Sub
--------------------



--
timconstan
------------------------------------------------------------------------
timconstan's Profile: http://www.excelforum.com/member.php...o&userid=15036
View this thread: http://www.excelforum.com/showthread...hreadid=266509