time auto entry

[BAKERSMAN]

I need to have the time and date auto populated in 2 different cells when a
value is enterd into a different cell. Here is the formula i have now

=IF(LEN(B2)=1,NOW(),"")

This formula works perfect but here is the problem I run into. Lets say cell
B2 equals "1", the date will populate in the cell the formula is entered
into. Lets says I enter "1" into cell B3 the next day, the cell with the
formula will populate the current date but the above cell's date will change
to the current date also. Is there a way to keep the date and times from
updating to the current time for all cells with this formula?

 

[Gary''s Student]

Say we are entering data in B2 thru B10 and want the date/time recorded in C2
thru C10.

But we want the date/time to be static.

Enter the following event macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("B2:B10")
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Offset(0, 1).Value <> "" Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Now
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[BAKERSMAN]

Here is my exact scenario. I need the Date to populate in colum C and I need
the time to populate in column D. I have coppied the macro into the
worksheet. Do I need to keep my formula in the cells that I want the static
data to stay in?

 

[Gary''s Student]

You don't need any formulas. Leave the col D & C area completely empty.
Discard the previous version. Here is the new version:

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("B2:B10")
If Intersect(r, t) Is Nothing Then Exit Sub
If t.Offset(0, 1).Value <> "" Then Exit Sub
Application.EnableEvents = False
t.Offset(0, 1).Value = Date
t.Offset(0, 2).Value = Time
Application.EnableEvents = True
End Sub

You should adjust the "B2:B10" to suit your needs.

 

[BAKERSMAN]

That worked perfect. One more question.

The purpose of the spreadsheet is to scan a barcode and get the exact date &
time the barcode was scanned. I think I am almost there with your help. I
have a separate worksheet I scan all these barcodes into. Then I have
formulas that break down the values in the barcode and put them into separate
cells. right now the macro is working off of a range of cells that require
human data entry. Is there a way to have the macro work off of lets say colum
"M"? This colum is populated once the barcode is scanned. Here is the current
formula in colum "M".

=RIGHT(LEFT($A2,36),8)

I guess what I am trying to get to is, once the barcode is scanned, I would
like to have the date and time appear automatically. Right now it requires
human data entry into one cell. Thanks for your help.

 

[Gary''s Student]

Can we have the formula in M2, say, display a blank until A2 is filled??

The previous macro only works with cells that change with human input and
not as a result of calculation. We can make a different macro. The new
macro will look for a blank cell becoming non-blank and then save the
date/time.

 

[BAKERSMAN]

That will work perfect. Unfortunately I do no know how to have M2 display a
blank until A2 is filled.

 

[Gary''s Student]

If $A2 is empty, then so should M2 be empty.

Throw away all the previous versons.

Use this:

Private Sub Worksheet_Calculate()
Set r = Range("M2:M100")
For Each m In r
If m.Value <> "" And m.Offset(0, -10).Value = "" Then
Application.EnableEvents = False
Cells(m.Row, "C").Value = Date
Cells(m.Row, "D").Value = Time
Application.EnableEvents = True
End If
Next
End Sub

 

[BAKERSMAN]

Works perfect!!

Thanks

If $A2 is empty, then so should M2 be empty.

Throw away all the previous versons.

Use this:

Private Sub Worksheet_Calculate()
Set r = Range("M2:M100")
For Each m In r
If m.Value <> "" And m.Offset(0, -10).Value = "" Then
Application.EnableEvents = False
Cells(m.Row, "C").Value = Date
Cells(m.Row, "D").Value = Time
Application.EnableEvents = True
End If
Next
End Sub

 

[Cathy]

I'm doing something similar and can't quite get it to work. I used your code
and modified for my spreadsheet. I want col B (date) and col C (time) to
auto entry when someone manually enters an item in column A from a list
(name). But, it's still updating with the current date/time instead of
staying static. Any suggestions? Do I not have the code quite right? Thanks

Private Sub Worksheet_Calculate()
Set r = Range("a2:a100")
For Each a In r
If a.Value <> "" And a.Offset(0, -10).Value = "" Then
Application.EnableEvents = False
Cells(a.Row, "b").Value = Date
Cells(a.Row, "c").Value = Time
Application.EnableEvents = True
End If
Next
End Sub

 

[Gord Dibben]

Wrong type of code for what you want.

The For Each will update every cell whenever a calculation takes place.

Try this sheet_change version.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Application.Intersect(Range("a2:a100"), Target) Is Nothing Then
n = Target.Row
If Me.Range("A" & n).Value <> "" Then
Me.Range("B" & n).Value = Format(Date, "dd mm yyyy")
Me.Range("C" & n).Value = Format(Time, "hh:mm:ss")
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[Cathy]

This is working great! Thank you for your help.