Move cursor to a specific cell after data has been entered

The2ndQuest

New Member
Joined
Nov 2, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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):

VBA Code:
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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

VBA Code:
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
VBA Code:
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)
VBA Code:
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
 
Upvote 1
Solution
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

VBA Code:
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
VBA Code:
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)
VBA Code:
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.
 
Upvote 0
You are welcome. Thanks for the follow-up. :)
 
Upvote 0

Forum statistics

Threads
1,224,595
Messages
6,179,798
Members
452,943
Latest member
Newbie4296

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top