worksheet_change event fires multiple times

[timconstan]

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

The worksheet_change event is NOT going off until the user enters
valid time, and then it is going off twice if the user first entered a
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).Offset(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 Su

 

[Tom Ogilvy]

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 3rd time
it fires, it would not be seen as a change?
--
Regards,
Tom Ogilvy

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 entered 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).Offset(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