Move to adjacent cell after barcode scan?

[JumboCactuar]

Hi,
A barcode scan currently moves down to the cell below.

Is it possible to do this with VBA?
A2: Contents of barcode scan is entered here
B2: Scanned time placed here
C2: Cell selected for user input

I know you can change cell direction in options but this affects all spreadsheets.

Appreciate any help

 

[jmacleary]

Put this code in your worksheet code area: - Provided your scanner is set up to do an 'enter' this will do what you want, alternatively just hit enter once the scan is done.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$2" Then
   Range("B2").Value = Time()
   Range("C2").Select
End If
Application.EnableEvents = True
End Sub

 

[JumboCactuar]

Put this code in your worksheet code area: - Provided your scanner is set up to do an 'enter' this will do what you want, alternatively just hit enter once the scan is done.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$2" Then
   Range("B2").Value = Time()
   Range("C2").Select
End If
Application.EnableEvents = True
End Sub

Thankyou this works great

Though unsure how to modify to work through the whole columns.

Example after C2 data input, it jumps to A3 awaiting next scan.

 

[jmacleary]

This will move to the next rows a column when column C is changed. It works for any row.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Left(Target.Address, 3) = "$A$" Then
    
   Range("B" & Target.Row).Value = Time()
   Range("C" & Target.Row).Select
ElseIf Left(Target.Address, 3) = "$C$" Then
   Range("A" & Target.Row + 1).Select
End If
Application.EnableEvents = True
End Sub

 

[JumboCactuar]

Perfect thankyou

Just one small blip which im unsure can be avoided.

If you clear the cells in these columns It still triggers the change event and places time

 

[jmacleary]

This will check to see if more than 1 cell has been selected - if so it wont do anything. You could develop the 'If Target.Cells.Count=1' line to have multiple tests to catch events that you dont want to have the code run for.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
    If Left(Target.Address, 3) = "$A$" Then
        
       Range("B" & Target.Row).Value = Time()
       Range("C" & Target.Row).Select
    ElseIf Left(Target.Address, 3) = "$C$" Then
       Range("A" & Target.Row + 1).Select
    End If
End If
Application.EnableEvents = True
End Sub

 

[nixonphotos]

Hi,
A barcode scan currently moves down to the cell below.

Is it possible to do this with VBA?
A2: Contents of barcode scan is entered here
B2: Scanned time placed here
C2: Cell selected for user input

I know you can change cell direction in options but this affects all spreadsheets.

Appreciate any help

I know this is an old post...and you may not still be here but I have a barcode question that I have searched for and can not find the answer. I have everything working on my spreadsheet except for the equipment that I scan is in no practical order. so when I scan the barcode it does not search for that equipment in C2-C200. how did you fix that? right now the only way it will work is if I scan all 200 pieces in order as it is in my spreadsheet. I would be interested on your input on this. I have everything else working except the searching the cell for that barcode scan and it adding the information scanned in the cell A2-A200.