Guide cursor

  • Thread starter Thread starter Gerard Sanchez
  • Start date Start date
G

Gerard Sanchez

Hi,

I am making a form for excel.
I was wondering if there's a way to make excel go to a specific cell after
pressing the enter button.

i.e. from B42 then immediately to E3 without having to point and click your
mouse to get there.?

Thanks!
 
Without using VBA the only way I can think of is to 'Lock' columns A thro' D
except cell B42 and 'Lock' cells E1 & E2. Uncheck "Select locked cells". Now
after making an entry in cell B42 press 'Enter' and your cursor will go to E3.
Slight problem of course you can't change or make entries in cells that are
locked!
I sure somebody will come up with a VBA solution for you.
 
It didn't work.
Please help with the VBA code


Ron@Buy said:
Without using VBA the only way I can think of is to 'Lock' columns A thro'
D
except cell B42 and 'Lock' cells E1 & E2. Uncheck "Select locked cells".
Now
after making an entry in cell B42 press 'Enter' and your cursor will go to
E3.
Slight problem of course you can't change or make entries in cells that
are
locked!
I sure somebody will come up with a VBA solution for you.
Click to expand...
 
Hi Gerard

The following is event code and belongs on the Sheet where you are working.
After making any change to the value in B42, the cursor will jump to cell E3

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Set myCell = Range("B42")

If Not Intersect(Target, myCell) Is Nothing Then
Range("E3").Activate
End If

End Sub

Copy the code
Right click on your Sheet tab>View Code
Paste the Code into the white pane that appears
Alt+F11 to return to Excel
 
In addition to Roger's code you may want to move to several cells in order.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells.....edit to suit
aTabOrd = Array("E3", "B51", "D10", "C11", "F34")

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i

End Sub


Gord Dibben MS Excel MVP
 
Hi,

Just a quick follow up.

I pasted the code and from what I understand, from Cell E3 it jumps to B51,
then to D10, then to C11 then to F34.

How would I make it so that one can key in entries from cell B3 . .B4 . .
..B5 up to B36, Then jumps to E3 . . .E4 . . .E5 up to E36, then jumps to H3
.. . .H4 . . .H5 up to H34 ?

Thank you soooo much!

Gerard
 
Hi Gerard

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRow As Long, myCol As Long

Set myCell = Range("B3:H36")
myRow = Target.Row
myCol = Target.Column

If Not Intersect(Target, myCell) Is Nothing Then

If myRow = 36 Then
myRow = 2: myCol = myCol + 1
End If
Cells(myRow + 1, myCol).Activate
End If

End Sub
 
Thank you sooo much Roger!


Roger Govier said:
Hi Gerard

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Dim myRow As Long, myCol As Long

Set myCell = Range("B3:H36")
myRow = Target.Row
myCol = Target.Column

If Not Intersect(Target, myCell) Is Nothing Then

If myRow = 36 Then
myRow = 2: myCol = myCol + 1
End If
Cells(myRow + 1, myCol).Activate
End If

End Sub
Click to expand...
 
Back
Top