having a date and time stamp automatically fill in based on a condition

[lladdis]

example

On page 1 a1 is a cell that will have a barcode scanned into it in live time.

when we populate cell A1 with a bar code scan, I want cell b2 to be the date stamp and cell c2 to be the time stamp. I would like these fields to autofill when cell A1 sees a scan.


I am set up to scan into the sheet but at this point I am not sure how to do the above.

Thanks
Eric

 

[Smitty]

Welcome to the Board!

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A:A")<br>             <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">With</SPAN> Target<br>                .Offset(, 1) = <SPAN style="color:#00007F">Date</SPAN><br>                .Offset(, 2) = Time<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

 

[lladdis]

I have some skills in excel but am far from an expert. It would appeat to me that I would do this as a macro. (that is new to me)

Could someone please confirm?

Thanks

 

[Smitty]

Yes, it's a macro.

To use it, right-click on the sheet tab and select View Code, then paste the above code in the new window on the right. Alt+Q will exit back to Excel. Whenever a change is made in column A on that sheet, the date/time stamps will be entered.

 

[lladdis]

This works. Thanks !