Focus on one cell

D

_dave

New member
Joined
May 11, 2016
Messages
21
Reaction score
0
Points
0
Excel Version(s)
2013
I have a spreadsheet that calculates a value based on an input. After entering a value is there a way to keep the focus on the input field for the next input?
 
Instead of pressing Enter after entering the value, press Ctrl+Enter. The focus will remain in the same cell.

You can also turn off moving the selection after pressing Enter by going to File|Options|Advanced and untick the box After pressing Enter, move selection:
2017-10-09_111723.JPG
 
.
If, for example, the input field were E5 ... you could paste this into the Sheet Level Module:

Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Me.Range("E5").Select
End Sub

Keep in mind the above macro will ALWAYS keep the focus on E5 ... even if you click on another location in Sheet1, the cursor will return to E5.
 
in the sheet module
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count > 1 Then Exit Sub
    ' change the following address to the input cell
    If Target.Address <> "$E$5" Then Exit Sub
    
    Application.EnableEvents = False
    'blank the input cell
        Target.Value = ""
    'select the input cell again
        Target.Select
    Application.EnableEvents = True
    
End Sub
 
or even:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" Then Target.Select
End Sub

or if there are several cells which need to behave in this way then you can:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then If Not Intersect(Target, Range("$F$8,$F$11,$G$11,$G$13,$E$5")) Is Nothing Then Target.Select
End Sub
which will restrict it to cells F8, F11, G11, G13 & E5
 
Last edited:
You must log in or register to reply here.
Back
Top