automatically insert date when I scan an item

[Guest]

I just bought a scanner for the small inventory that I have and I am using
excel 2003. When I scan an item it inserts the barcode in one column. I would
like excel to automaticaly insert the date in the proceding column. How would
I accomplish this, Thanks!

 

[Gord Dibben]

Greg

I don't know if event code works with a scanner or not but try this which places
the date into column A when B is populated.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("B:B")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, -1).Value = Format(Now, "dd mmm yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.


Gord Dibben MS Excel MVP

 

[Guest]

Thanks a lot, It works with the scanner. Quick question, the serials are
being inserted in column A and I want the date in column B. I tried to change
the code my self but can not get it to work. Would do I need to change?
Thanks

 

[Gord Dibben]

Greg

The work "proceding" in your original post threw me. I thought you meant
"preceding".

Here is amended code which places a date in column B as column A is populated.
See the changes at theTarget, Me.Range to A:A and at the offset(0, 1)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then
With Target
If .Value <> "" Then
.Offset(0, 1).Value = Format(Now, "dd mmm yyyy")
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


Gord