Enter Key

[bw]

We can use Tools>Options>Edit>Direction to control the behaviour of the
Enter Key, but is there a way to have the Enter Key stay in the same cell?

I have removed the check mark from "Move Selection after Enter" to no
avail.

Thanks,
Bernie

 

[Nick Hodge]

Bernie

Unchecking should do it but...

You can certainly set this via code. This could either be in the workbooks
open even or you could use it as a toolbar button and toggle it on/off (as
per the code sample)

Sub moveAfterReturnNo()
If Application.MoveAfterReturn = False Then
Application.MoveAfterReturn = xlDown
Exit Sub
End If
Application.MoveAfterReturn = False
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[bw]

Thanks, Nick!

For some reason, "Unchecking" now works??? In any case, I appreciate the
code, and I'll save it for possible later use.

Bernie

 

[bw]

Nick,

I discovered why "Unchecking" didn't work. The Sheet is Protected, and
"Unchecking" doesn't work when the sheet is protected.

Also, "If ActiveSheet.Protect = True Then" is always False.

Do you have a solution to this problem?

Thanks,
Bernie

 

[Tom Ogilvy]

When you do Tools=>Protect=>worksheet, you have 3 choices. So there are
three properties to check (plus there is one more property that can be
protected, but only with code).

Worksheet object properties:
protectionMode
protectContents
protectscenarios
protectDrawingObjects

if any of them are true, then the sheet is protected.

 

[bw]

Thanks for the information, Tom, but when the sheet is PROTECTED, and
"Move Selection after Enter" is NOT checked, the Enter Key Moves to a
different cell (Right, Left, Down, or Up...whichever was last selected).

You don't need code to verify the statement above.

But still, I have the following code anyway (now), and it doesn't work
either.

Sub NoMoveAfterReturn()
If ActiveSheet.ProtectContents = True Then 'PROTECTED
ActiveSheet.Unprotect
Application.MoveAfterReturn = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
Else 'UNPROTECTED
Application.MoveAfterReturn = False
End If
End Sub

I'm still looking for a suggestion that will work when the sheet is
protected.

Thanks,
Bernie

 

[Tom Ogilvy]

Thanks for the information, Tom, but when the sheet is PROTECTED, and

"Move Selection after Enter" is NOT checked, the Enter Key Moves to a
different cell (Right, Left, Down, or Up...whichever was last selected).

I couldn't reproduce that behavior. When I unchecked it and edited an
unlocked cell (protected sheet), when I hit enter, the highlight did not
move. Maybe it isn't clear what you are saying.

 

[bw]

I don't understand why you can make it work, and I can't?

I format cells A1:A3 as "Unlocked", then when I Protect the Sheet and
"Select Unlocked Cells" only.

I then uncheck "Move selection on Enter".

When I enter information into cell A1 and press Enter, the focus moves to
Cell A2.

Again, I don't understand why you can make it work, and I can't?

Bernie

 

[Tom Ogilvy]

I could reproduce the behavior if I set the enableselection property of the
worksheet to xlUnlockedCells. If no restriction, it worked as expected.