Move cursor to a specific cell after data has been entered

[The2ndQuest]

What I'm trying to do is tell a cell to move the cursor to a specific cell after data has been entered. This is basically so that I can use a barcode scanner to scan two barcodes off a box into their respective columns, then go to the next row and repeat the process and avoid having to use the keyboard or mouse between barcodes/boxes. The scanner already automatically hits enter after scanning the barcode. And I do not want to alter any universal settings for Excel cursor movement.

Now, I've managed to figure out how to do some macros in the past via a button or object, but never something auto-running looking at cells. But I've pieced together some idea of the direction to go with two operations. What I want to happen for the first one is if data is entered into C12, then the cursor moves to E12. And the second one is if data is entered into E12, then the cursor moves to F12. And then I can just swap out the cell addresses for the next 12+ rows (probably a way to iterate that in code, but figured I'd just keep it simple and go one by one since there's only a dozen or so rows, so problems can be focused on the more complex cursor code).

What I've pieced together form different google searches was to add this to the sheet in VB (this is just for the E12 to F12 cursor movement, but I can probably figure out how to do the other half once I know how this one works):

Private Sub Worksheet_Activate()
'
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "E12" And Target.Address <> "" Then
        Target.Offset(-2, -1).Select
End If

End Sub

The first part supposedly would auto-run this when the sheet is opened, and the latter would monitor for changes to the cells (by checking if they are not blank). But that either doesn't work or I haven't been able to figure out how to place/run it within the sheet (if it's not automatically done so through the VB project screen- I'm unclear on how they interact, or if there is a "compile/add to sheet" process or something I'm not doing).

Any guidance/suggestions are appreciated.

-Billy

 

[The2ndQuest]

Whhops, that should say moving from "E12" to "C13" (not "F12").

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this with a copy of your workbook

1. Remove any Worksheet_Activate or Worksheet_Change code you have in the relevant worksheet module

2. Put this code in the ThisWorkbook module

Private Sub Workbook_Open()
  SetActiveCell
End Sub

3. Put this code in a general module. make sure the sheet name in this code is the name of the sheet you want all this to happen on

Sub SetActiveCell()
  Application.Goto Reference:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
End Sub

4. Put these codes in the relevant worksheet's module (it was 'Sheet1' for me)

Private Sub Worksheet_Activate()
  SetActiveCell
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    Select Case Target.Column
      Case 3 'Column C
        Target.Offset(, 2).Select
      Case 5 'Column E
        Target.Offset(1, -2).Select
    End Select
  End If
End Sub

 

[The2ndQuest]

Welcome to the MrExcel board!

Try this with a copy of your workbook

1. Remove any Worksheet_Activate or Worksheet_Change code you have in the relevant worksheet module

2. Put this code in the ThisWorkbook module

Private Sub Workbook_Open()
  SetActiveCell
End Sub

3. Put this code in a general module. make sure the sheet name in this code is the name of the sheet you want all this to happen on

Sub SetActiveCell()
  Application.Goto Reference:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)
End Sub

4. Put these codes in the relevant worksheet's module (it was 'Sheet1' for me)

Private Sub Worksheet_Activate()
  SetActiveCell
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    Select Case Target.Column
      Case 3 'Column C
        Target.Offset(, 2).Select
      Case 5 'Column E
        Target.Offset(1, -2).Select
    End Select
  End If
End Sub

That works perfectly! Thank you.

 

[Peter_SSs]

You are welcome. Thanks for the follow-up.