Auto fill date & time

rbobcat1

New Member
Joined
Dec 19, 2011
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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:
Code:
[COLOR=#333333]If rCell > "" Then[/COLOR]
to
Code:
[COLOR=#333333]If (rCell > "") And (rCell.Offset(0,1)="") Then[/COLOR]
and then change:
Code:
[COLOR=#333333]If rCell = "Closed" Then[/COLOR]
to
Code:
[COLOR=#333333]If (rCell [/COLOR][COLOR=#333333]= "Closed"[/COLOR][COLOR=#333333]) And (rCell.Offset(0,5)="") Then[/COLOR]
 
Upvote 0
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
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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....[TABLE="width: 78"]
<tbody>[TR]
[/TR]
[TR]
[/TR]
[TR]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You are welcome.
Glad to hear it worked out for you.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top