Results 1 to 8 of 8
  1. #1
    Bronze Lounger IanWilson's Avatar
    Join Date
    Dec 2000
    Location
    Bristol, United Kingdom
    Posts
    1,523
    Thanks
    0
    Thanked 1 Time in 1 Post

    Order of moving in a form (2003)

    I have designed a form using Excel. (Actually it's an answer sheet for a test.) The user can only make entries in the unlocked cells, if the worksheet is protected.

    Using the tab key, the selection moves from one field to the next in the order I want. But using the Enter key, it doesn't. I have experimented with the option for which direction to move in on pressing enter. If that is set to Down, it doesn't go where I want it to. If it is set to Right, it does. If the box for that option is unchecked, then the selection doesn't move as long as the sheet is not protected. But when the sheet is protected (which is how it will be used) the selection moves on Enter in the direction that is shown greyed out in the options.

    As I understand it, the way the move selection on Enter option is set affects Excel on my computer, rather than being stored as a property of the worksheet, so if users don't have the option set the right way, when they use the test, the direction in which the selection moves may not be what I want.

    Is there any way in which I can ensure that the selection moves in the correct direction, independently of the settings in the user's options?

    Ian

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order of moving in a form (2003)

    You are correct that the action of the Enter key is a user-level setting, not a workbook-level setting. The default is that Enter moves down, and that is how it should be. Changing it would clash with the user's expectation of how Excel behaves. So I'd leave it alone, even if you don't like it.

  3. #3
    Bronze Lounger IanWilson's Avatar
    Join Date
    Dec 2000
    Location
    Bristol, United Kingdom
    Posts
    1,523
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Order of moving in a form (2003)

    Is there any way of perhaps having a macro that runs when this particular document is opened that changes the direction so that the test paper works like it should but reverts to normal behaviour afterwards?

    To illustrate the problem, in the attached sample, if you type your answer in Question 1, then press Enter it jumps to Question 8, if the direction on Enter is set to Down, but it moves to the first box of Question 2 if the direction is set to Right.

    Ian
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order of moving in a form (2003)

    You can put the following code in the ThisWorkbook module of the workbook:

    Private lngReturnDirection As Long

    Private Sub Workbook_Activate()
    lngReturnDirection = Application.MoveAfterReturnDirection
    Application.MoveAfterReturnDirection = xlToRight
    End Sub

    Private Sub Workbook_Deactivate()
    If lngReturnDirection Then
    Application.MoveAfterReturnDirection = lngReturnDirection
    Else
    Application.MoveAfterReturnDirection = xlToRight
    End If
    End Sub

    Of course, this means that the user will get the usual macro warning when opening this workbook. If he/she disables macros (or if macro security is set to High), the code will not work.

  5. #5
    Bronze Lounger IanWilson's Avatar
    Join Date
    Dec 2000
    Location
    Bristol, United Kingdom
    Posts
    1,523
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Order of moving in a form (2003)

    Thanks - I'll try that at work tomorrow.

    Ian

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Order of moving in a form (2003)

    Here is an corrected and expanded version that also takes the Move after Return setting into account:

    Private blnMoveAfterReturn As Boolean
    Private lngMoveAfterReturnDirection As Long

    Private Sub Workbook_Activate()
    blnMoveAfterReturn = Application.MoveAfterReturn
    lngMoveAfterReturnDirection = Application.MoveAfterReturnDirection
    Application.MoveAfterReturn = True
    Application.MoveAfterReturnDirection = xlToRight
    End Sub

    Private Sub Workbook_Deactivate()
    If lngMoveAfterReturnDirection Then
    Application.MoveAfterReturnDirection = lngMoveAfterReturnDirection
    Application.MoveAfterReturn = blnMoveAfterReturn
    Else
    Application.MoveAfterReturnDirection = xlDown
    Application.MoveAfterReturn = True
    End If
    End Sub

    As before, the code should go into the ThisWorkbook module.

  7. #7
    Bronze Lounger IanWilson's Avatar
    Join Date
    Dec 2000
    Location
    Bristol, United Kingdom
    Posts
    1,523
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Order of moving in a form (2003)

    Thanks. - Ian

  8. #8
    Bronze Lounger IanWilson's Avatar
    Join Date
    Dec 2000
    Location
    Bristol, United Kingdom
    Posts
    1,523
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Order of moving in a form (2003)

    Hans - this worked just fine. Thanks again.

    Ian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •