I know the enter key's next cell advance behavior can be addressed in workbook options, but how would I adjust it on a sheet by sheet basis?

On sheet entry I would like to do two things, record the current enter key configuration and then change it to, for example, advance down columns.

On sheet exit, I would like to return the key to its original state.

Thank you

Copy these two events to a sheet module.

Private Sub Worksheet_Activate()
Application.MoveAfterReturnDirection = xlToRight
End Sub


Private Sub Worksheet_Deactivate()
Application.MoveAfterReturnDirection = xlDown
End Sub


Gord


On Wed, 9 Jul 2014 10:40:15 -0700 (PDT), wrote:

I know the enter key's next cell advance behavior can be addressed in workbook options, but how would I adjust it on a sheet by sheet basis?

On sheet entry I would like to do two things, record the current enter key configuration and then change it to, for example, advance down columns.

On sheet exit, I would like to return the key to its original state.

Thank you

On 07/09/2014 1:40 PM, wrote:
I know the enter key's next cell advance behavior can be addressed in workbook options, but how would I adjust it on a sheet by sheet basis?

On sheet entry I would like to do two things, record the current enter key configuration and then change it to, for example, advance down columns.

On sheet exit, I would like to return the key to its original state.

Thank you

My inclination is to store sheet-specific behavior in a local scope
defined name that code can read/evaluate as to which direction (if any)
to move.

Normally, I would store default MoveAfterReturn and
MoveAfterReturnDirection in global variables so I can restore these when
my workbook deactivates.

So in the code window behind ThisWorkbook...

Option Explicit

Dim glDirMove As XlDirection, gbDirMove As Boolean

Private Sub Workbook_Activate()
Dim sDir$

'Store default settings
With Application
gbDirMove = .MoveAfterReturn
glDirMove = .MoveAfterReturnDirection
End With 'Application
On Error Resume Next '//if name doesn't exist
sDir = Mid(ActiveSheet.Names("uiDirMove").RefersTo, 2)
If sDir < "" Then SetEnterKeyBehavior sDir
End Sub

Private Sub Workbook_Deactivate()
On Error Resume Next '//if globals lost values
With Application
.MoveAfterReturnDirection = glDirMove
.MoveAfterReturn = gbDirMove
End With 'Application
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sDir$
On Error Resume Next '//if name doesn't exist
sDir = Mid(Sh.Names("uiDirMove").RefersTo, 2)
If sDir < "" Then SetEnterKeyBehavior sDir
End Sub


In a standard module...

Option Explicit


Sub SetEnterKeyBehavior(sDir$)
With Application
If sDir = "none" Then .MoveAfterReturn = False: Exit Sub

.MoveAfterReturn = True
Select Case sDir
Case "dn": .MoveAfterReturnDirection = xlDown
Case "up": .MoveAfterReturnDirection = xlUp
Case "lt": .MoveAfterReturnDirection = xlToLeft
Case "rt": .MoveAfterReturnDirection = xlToRight
End Select
End With 'Application
End Sub

...where the local scope defined name "uiDirMove" RefersTo is entered as
one of the following direction codes...

=none
=dn
=up
=lt
=rt

...and the "ui" prefix denotes a User Interface setting!

--
-
Garry

Free Usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Oops! Where the sheet-specific MoveAfterEnter needs to be default,
change Workbook_SheetActivate as follows...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sDirMove$
On Error Resume Next '//if name doesn't exist
sDirMove = Mid(Sh.Names("uiDirMove").RefersTo, 2)
'Restore default if no change needed
If sDirMove < "" Then SetEnterKeyBehavior sDirMove _
Else Call Workbook_Deactivate
End Sub

Also, note that I changed the var sDir to sDirMove for naming convention
consistency!
--
-
Garry

Free Usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Gord Dibben
GS

thank you all.