I recently got a new (to me) PC at work. I went from 512 of RAM to 1G, which is quite nice. Anyway, I used the post and comments on Power User Setup to get Excel humming the way it was. One comment, by John Walkenbach, was that he unchecks the Move Selection After Enter box on the Edit menu of Tools > Options. Another commenter agreed that unchecked was the way to go. I’ve always had that checkbox checked, but it was time to try a new way. For a week, or so, I’ve worked with that box unchecked and I have to say that it was much easier…about half the time. It seems that half the time I want to remain in the cell I just edited and the other half I want to move down.
My new plan is to put a macro in Personal.xls (that will make three macros in there) to switch between the behaviors.
Application.MoveAfterReturn = Not Application.MoveAfterReturn
End Sub
I assigned it a shortcut key (Tools > Macros > Macros > Options) of Control+Shift+M. Now I can switch back and forth. We’ll see how that goes.
Update: Mike Schaeffer shows some improvements here. I haven’t seen Mike’s blog before, but a quick scan of the post titles looks promising. I wanted to make sure there was a link to his blog in the main post rather than hidden in the comments.
The only thing I don’t like about Mike’s improvements is that it seems backward. It seems as if I’m punished for choosing correctly, i.e. I use the key combination to change the property and if it was the proper thing to do, I have to press it again. If the property was already set properly, I don’t have to do anything. I think it should be the other way around, so I propose these changes:
With Application
.MoveAfterReturn = Not .MoveAfterReturn
.StatusBar = “Move After Return Is “ & IIf(.MoveAfterReturn, “Enabled”, “Disabled”)
.OnTime Now + TimeValue(“00:00:03”), “ResetStatusBar”
End With
End Sub
Sub ResetStatusBar()
Application.StatusBar = False
End Sub
In this scenario, I hit Control+Shift+M and look at the Status Bar. If I changed the property to what I want, I’m rewarded by not having to do anything. If I change the property away from what I want, I’m punished by having to press the keys again. Such are the delusions of some cosmic justice in my pathetic world.
There are Tools – Options settings that should just be there at a click or, at the most, two. But which ones is probably a question of style and habits. I toggle A1-R1C1 countless times every day, so that’s my most frequently used personal macro.
Another one is “Reset Excel”, code setting calculation to automatic, enable selection all, enable events true, application statusbar false and all those other little things that my new and crashed code intended to reset at the end if I’d written it right from the beginning.
“I toggle A1-R1C1?
Really? I’ve never used R1C1.
“Another one is “Reset Excel””
Ooh, that’s a good idea. I’ve been doing it in the Immediate Window – but no more.
Public Sub ResetExcel()
On Error Resume Next
Application.EnableEvents = True
Application.StatusBar = False
Application.Calculation = xlAutomatic
Application.Cursor = xlDefault
Application.ReferenceStyle = xlA1
ActiveSheet.EnableSelection = xlNoRestrictions
ActiveSheet.ClearArrows
ActiveSheet.DisplayAutomaticPageBreaks = False
End Sub
And of course:
Sub restore()
Dim obar As CommandBar
For Each obar In Application.CommandBars
obar.Enabled = True
Next
End Sub
I always keep Move Selection off. If I want to move down (or right) after each entry, I click the arrow key instead of Enter. It’s worked for over a decade.
My procedure I run to fix calc, screen updating, enable events, cursor, etc., is called “oops”, so it’s easy to find.
One suggestion: have your macro dump out the current state to the status bar when it runs. That way you can more easily tell what mode you just entered.
Refinement two (more difficult): Have the macro set up such that the first time you press it, it displays the current state. The second time you execute it within half a second or so, it toggles the state.
I wrote up refinement two on my website, linked above. VBA made it pretty easy….
I have one to toggle Word Wrap.
Hello –
Hoping for some help: Excel 2003 is defaulting movement after Enter to the next line down (normal for me), but several columns to the right, as if it’s intuiting the table of data.
I have Tools-Options-Move selection after Enter checked for Down, but I’m getting movement from D5 to B6 instead of from D5 to D6 (for example).
Any ideas would be very much appreciated!
Thank you!
Amy,
Were you originally in B5 and hit Tab twice to get to D5 before hitting Enter? That is the way Excel works, which can be quite helpful if you’re entering records of multiple fields each. Otherwise you’ll have to reset that ‘feature’ by using an arrow key (either use the right arrow to get to D initially, or just hit the down arrow from D if you used the tab before it).
Matt