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" wrote:
>
> 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.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "RefLib1978" <(E-Mail Removed)> wrote in message
> news:310305C1-48D2-45C0-A525-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "RefLib1978" <(E-Mail Removed)> wrote in message
> >> news:9AFEF115-EBAB-430D-A619-(E-Mail Removed)...
> >> > 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
> >>
> >>
>
>
Similar Threads
