Auto Date that doesn't change

[RefLib1978]

Hi - I'm sorry if this is a duplicate question. I was unable to find the
answer in a search and thought that I would submit the question.

The question is two parts.

First, I have a table that uses A-I columns. The purpose of the table is to
track purchases. I would like to have a date automatically entered into
column A if I put information into B. Then to have a second date that enters
a date into H if I enter information into I. The date in A is for the day the
order was placed and the date in H is the date it arrived.

The second part is that I would like the dates to not change after their
created. I've been using a macro for column A:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(Range("B:B"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
..Offset(0, -1).ClearContents
Else
With .Offset(0, -1)
..NumberFormat = "mm/dd/yyyy"
..Value = Now
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

But I have found that the date changes if I do anything (including highlight
the text) of column B. Is it possible to prevent that from happening?

Thanks for the help.
C. Nichols

 

[Don Guillett]

if target.column=

 

[Don Guillett]

try this instead
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("b1,i1").EntireColumn) Is Nothing Then Exit Sub
If Len(Application.Trim((Target))) < 1 Then
Target.Offset(, -1) = ""
Else
Target.Offset(, -1) = Date
End If
End Sub

 

[RefLib1978]

The macro is putting in the dates perfectly, but still A is still updating if
I highlight or do anything in B. I shouldn't need to fiddle with the I column
after I enter the data and if it does update later, it's not problem. With B,
I find that I do need to copy and paste the text and plugging it in elsewhere
later, but I'll still need that original date. Is it possible to keep it from
updating?

Thanks for helping me. I really appreciate it.
C. Nichols

 

[Don Guillett]

I'm not quite sure what you want.
If you enter something or touch the delete key or the space bar the macro
will fire for the column if it is col B or I.
delete or spacebar will erase column to left. Any other entry will put the
date to the left.

 

[RefLib1978]

I'm sorry. I should have included more description about what I'm trying to
do. I order books for the library where I work. To ensure that everything I
order a) arrives and b) doesn't exceed my budget, I've set up a spreadsheet
to track the orders. Column A is the Order date. B is the title. Columns C-G
are information about the item (author, who ordered it, cost, etc). Then H is
the Date Received and I is the final cost.

When I order items, I enter the information about the book except for the
final cost (I) and date received (H). After a couple of weeks pass, I will go
to the library's catalog to check the status of the order.When I do that, I
copy and paste the title into the library's database. Unfortunately when I do
that, the date automatically updates to the new day, rather than staying the
same. So if I order something in July, but check it in September, the
spreadsheet will look like I ordered the item in September rather than in
July. I wanted the date to stay the July date even if I copy and paste later.

Is that even possible? I can stop copying and pasting, I was just hoping to
save time by doing it that way.

Thanks for helping me.
C. Nichols

 

[Don Guillett]

I don't see the problem. Send a workbook to my address below if you like
along with before/after examples