Enter key

[george.dick]

I have Office-X and there is one major frustration for me.

It's real important to use the Enter key to accept an entry and go DOWN
to the next cell. I need to do this when protection is ON. As near as
I can determine, I can control the operation of the Return key but not
the Enter key.

Am I missing something or is this a limitation of the software?

 

[JE McGimpsey]

I have Office-X and there is one major frustration for me.

It's real important to use the Enter key to accept an entry and go DOWN
to the next cell. I need to do this when protection is ON. As near as
I can determine, I can control the operation of the Return key but not
the Enter key.

Am I missing something or is this a limitation of the software?

By default, the Return and Enter keys perform the same movement. For a
workaround, see

http://www.mcgimpsey.com/excel/enterkey.html

 

[Socrates]

Thank you very much.

The following is according to your link:
If you check the Move selection on Return checkbox in Preferences/Edit and
set the direction dropdown to "down", both the Return key and the Enter key
cause the selection to move down.

That's exactly what I want but it's not working that way. The pref affects
ONLY the Return key. I'm not at my Mac right now so I can't test but, if I
remember correctly, the pref does work that way if protection is OFF.
However, when protection is ON, the Enter key ALWAYS moves the selection to
the right.

 

[JE McGimpsey]

If you check the Move selection on Return checkbox in Preferences/Edit and
set the direction dropdown to "down", both the Return key and the Enter key
cause the selection to move down.

That's exactly what I want but it's not working that way. The pref affects
ONLY the Return key. I'm not at my Mac right now so I can't test but, if I
remember correctly, the pref does work that way if protection is OFF.
However, when protection is ON, the Enter key ALWAYS moves the selection to
the right.

Yup, that's right - protecting the sheet makes the Enter key stop
changing the selection. It appears to be design behavior - at least it's
worked that way since XL01, but I've asked MacBU why it works that way.
I'll post back if I get an answer...

However, here's a workaround I took from a previous project. It's not
particularly robust - while it works like the Return key for a single
cell selection or for a single multiple-cell selection, it doesn't work
for multiple areas within a selection, though it shouldn't be hard to
modify it.

The protection state of the worksheet doesn't affect the macros'
operation.

Save this in a new workbook as an add-in, or download my example add-in
at

ftp://ftp.mcgimpsey.com/excel/moveright.xla

and install it (Tools/Add-ins...).

Put these in the ThisWorkbook code module:

Private Sub Workbook_Open()
Application.OnKey "{Enter}", "MoveRight"
Application.OnKey "+{Enter}", "MoveLeft"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{Enter}", ""
Application.OnKey "+{Enter}", ""
End Sub


Put these in a regular code module:

Public Sub MoveRight()
If TypeOf Selection Is Range Then
With Selection
If .Count = 1 Then
If .Column <> .Parent.Columns.Count Then _
.Offset(0, 1).Activate
Else
If ActiveCell.Column < .Cells(.Count).Column Then
ActiveCell.Offset(, 1).Activate
Else
If ActiveCell.Row < .Cells(.Count).Row Then
ActiveCell.Offset(1, _
1 - .Columns.Count).Activate
Else
.Cells(1, 1).Activate
End If
End If
End If
End With
End If
End Sub

Public Sub MoveLeft()
If TypeOf Selection Is Range Then
With Selection
If .Count = 1 Then
If .Column <> 1 Then _
.Offset(0, -1).Activate
Else
If ActiveCell.Column > .Column Then
ActiveCell.Offset(, -1).Activate
Else
If ActiveCell.Row = .Row Then
.Cells(.Count).Activate
Else
ActiveCell.Offset(-1, _
.Columns.Count - 1).Activate
End If
End If
End If
End With
End If
End Sub

 

[Socrates]

Thanks very much. I'm hurting for time right now because of holiday
activities but I'll get to this as soon as I'm able. Several
questions:

1. I tried to get to your example using Safari but all I got was a
blank screen. Is there a different way to access ftp?

2. I'm not an experienced hacker. What are the "code modules?" Will
it be obvious when I create a new workbook?

3. Do I need to incorporate this code separately for each Excel
workbook that I create?

 

[Socrates]

I'm an idiot. I figured it out. Thanks.

 

[Socrates]

I'm an idiot. I figured it out. Thanks.

 

[Socrates]

It does the exact opposite of what I want.

Previously, the Enter key causes a right move on a protected sheet and
no move on an unprotected sheet. This solution results in a right move
in both situations. I want the Enter key to result in a down move on a
protected sheet.