automatically insert date when I scan an item
 

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!

automatically insert date when I scan an item
 

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

On Tue, 13 Feb 2007 13:38:01 -0800, GregB
wrote:

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!

automatically insert date when I scan an item
 

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" wrote:

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

On Tue, 13 Feb 2007 13:38:01 -0800, GregB
wrote:

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!

automatically insert date when I scan an item
 

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

On Thu, 15 Feb 2007 08:52:31 -0800, GregB
wrote:

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" wrote:

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

On Tue, 13 Feb 2007 13:38:01 -0800, GregB
wrote:

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!