MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Enter Key

Posted by Jim on August 04, 2001 9:33 AM

Does anyone know if it is possible in code that after
you enter an integer in a specific target address and
press the enter key it would jump to the next target

Posted by Mark W. on August 04, 2001 11:12 AM

Why use code? Can't you confine the movement of
the active cell by making an appropriate range
selection and by using the Tools | Options...
menu command to modify the "Move selection after
Enter" setting on the Edit tab?

Posted by Jim on August 04, 2001 11:43 AM

Sorry i didn't state my question correctly, what
i meant to ask was press enter key while in G46
and jump to A48

Posted by Mark W. on August 04, 2001 12:02 PM

Simple choose the Edit | Go To... menu command,
enter "A48,G46" in the Reference field, and press
[ OK ]. Now your selection is prepped for your
desired re-positioning of the active cell.

Posted by Jim on August 04, 2001 12:38 PM

Hi Mark,
something strange just happened it returned an error
message with "A48,G46" and then put in $B$23 and
$B$35 and it won't let me delete them?

Posted by Robb on August 05, 2001 5:16 AM

You could unlock each of the cells you want to go to,
and then protect your sheet. If you get the user to use only the tab key,
the selection only goes to unlocked cells.

Another way would be to put code in the "Change" event of
the worksheet. I would use "Select Case", with each case being a cell you want
to move to. You could also use this in tandem with the locked
cells I mentioned above to coordinate the tab key behaviour.
This code should work for you:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim myT As String
myT = Target.Address
Select Case myT
Case "$A$24"
Case "$B$12"
Case "$C$6"
Case "$A$3"
Case "$D$14"
Case "$A$1"
Case Else
End Select
End Sub

If your last case action selects the first case, your
user will keep going round the loop of cells.
Be careful with the Case strings though, you must use upper case.

Hope it helps.

Posted by Mark W. on August 05, 2001 2:37 PM

What was the error message? I presume that you mean
$B$23,$B$35 was added to the "Go to" list. I
suspect that those cells were selected at the
time you performed the Go To. The addition of
these to the list is normal behavior. Excel
keeps track of were you've been in the event that
you want to return using Edit | Go To... Hi Mark,