Auto fill date & time

[Rbobcat]

I came across the below VBA and need some help to modify it.
First it auto fills the date and time in I1 whenever you selected from a drop down in H1.
I have also added another to it that adds date and time to cell J1 whenever you choose "Closed" in cell E1.

This all works great for what I want with one exception, if H1 or E1 change by accident it updates the date and time in cells I1 and J1.
This is not what i want.
is there anyway to also force it to make the Date and Time in cells I1 and J1 permanent?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
Dim rChange As Range
Dim rChanges As Range

On Error GoTo ErrHandler
Set rChange = Intersect(Target, Range("H5:H1048576"))
If Not rChange Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChange
If rCell > "" Then
With rCell.Offset(0, 1)
.Value = Now
End With
Else
rCell.Offset(0, 1).Clear
End If
Next
End If

On Error GoTo ErrHandler
Set rChanges = Intersect(Target, Range("E5:E1048576"))
If Not rChanges Is Nothing Then
Application.EnableEvents = False
For Each rCell In rChanges
If rCell = "Closed" Then
With rCell.Offset(0, 5)
.Value = Now
End With
Else
End If
Next
End If

ExitHandler:
Set rCell = Nothing
Set rChange = Nothing
Set rChanges = Nothing
Application.EnableEvents = True
Exit Sub
ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub

 

[Joe4]

This all works great for what I want with one exception, if H1 or E1 change by accident it updates the date and time in cells I1 and J1.
This is not what i want.
is there anyway to also force it to make the Date and Time in cells I1 and J1 permanent?

What do you mean by "if H1 or E1 change by accident"?
How can you tell if they are changed "on purpose" or "by accident"?
Or, do you mean that if their "companion" cells already have a date/time stamp in there, do not overwrite it?
In other words, only put the date/time stamp value in the cell if the cell is blank?

If so, then just change the first:

[COLOR=#333333]If rCell > "" Then[/COLOR]

to

[COLOR=#333333]If (rCell > "") And (rCell.Offset(0,1)="") Then[/COLOR]

and then change:

[COLOR=#333333]If rCell = "Closed" Then[/COLOR]

to

[COLOR=#333333]If (rCell [/COLOR][COLOR=#333333]= "Closed"[/COLOR][COLOR=#333333]) And (rCell.Offset(0,5)="") Then[/COLOR]

 

[Rbobcat]

When I was working/testing the file I accidentally changed H1 and noticed it change I1 to the current date and time. same thing for the other.
J inserts D/T when E is changed to Closed, but also changes again if you make a different selection in E
I tried locking the cells but that only kept the VBA from working because the cell was locked

To give you some history of the file, it is a ToDo List.
Column E is a status of the line and Column H is who the Task was assigned to.
When the Status change to Closed it puts D/T in J

 

[Joe4]

But how can the computer determine what is an "accidental" change versus what is an "intended" change?
What is the logic for determining that?

Remember, computers are very literal. We need to tell them what to do, and exactly when to do it.
So we need to go up with "hard and fast" rules to govern the process.

 

[Rbobcat]

Your suggestion worked perfectly.
As far as "accidental" change versus what is an "intended" change?
The first selection in column H will never change once selected so that will be the reported start of the Task that reports in I.
But the status has multiply selection (Not Started, In-Progress and Closed) so it will only insert the D/T when "Closed" is selected in E.


Thank you....

<tbody>
</tbody>

 

[Joe4]

You are welcome.
Glad to hear it worked out for you.