Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Restrict cusor movement

    Hi,

    With Sheet1 selected i want the movements of cursor to be fixed from A1 and pressed enter to move it to B1 upto E1 in right side and once it reaches E1 and pressed enter it should turn back to A2

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    Goa,

    This code will restrict the selection of the cells though cells A1 to E1 using the enter Key or the right/down arrows. I hope this is what you are looking for.

    Maud

    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = True
    If ActiveCell.Cells.Count > 1 Then Exit Sub
    If Not Intersect(ActiveCell, Range("A2")) Is Nothing Then
        ActiveCell.Offset(-1, 1).Select
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("B2")) Is Nothing Then
        ActiveCell.Offset(-1, 1).Select
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("C2")) Is Nothing Then
        ActiveCell.Offset(-1, 1).Select
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("D2")) Is Nothing Then
        ActiveCell.Offset(-1, 1).Select
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("E2")) Is Nothing Then
        ActiveCell.Offset(-1, -4).Select
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("F1")) Is Nothing Then
        ActiveCell.Offset(0, -5).Select
    End If
    Done:
    Application.EnableEvents = True
    End Sub
    Attached Files Attached Files

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Goa,

    Another option would be to Unprotect (unselecting Locked) those cells (via the Format Cells option) then Protect the worksheet Unselecting the option (Select locked cells).
    This will do the trick with the exception of the returning to A2 part, it will return to A1 instead. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    RG,

    I came across the same stumbling block with code. Couldn't get it to go to A2 instead of A1. Anyway, your solution works better than mine does. But that's my motto, "code first; common sense later."

  5. #5
    New Lounger
    Join Date
    Sep 2013
    Posts
    17
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    Okay it goes from A1:E1 but i want the cursor to return in A2 after E1 and limit to end E200

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Goa,

    Ok, now I understand better I think.

    1. Unlock cells A1:E200
    2. File, Options, Advanced
    ExcelCursorDirection.JPG
    3. Protect the sheet and unselect option (Select Locked Cells)

    Now you can select all cells from A1:E200 and using the Enter key will move to the right until col E then move to Col A in the next row. HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Maudibe (2013-10-20)

  8. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    RG,

    There are 3 issues that may exist with that method: The worksheet has to be protected, shifting to the right affects all cells, and all cells outside the range are locked. If that is the intent, then your solution is 100%. If not then here is an alternative using the Application.OnKey method.

    Pressing the enter key or the right/down arrows will cycle the cursor in the same pattern without locking other cells, affecting their cursor movement, or needing to protect the sheet. Outside the range, the keys will function normally,

    Maud

    SHEET MODULE
    Code:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.OnKey "~", "EnterKey"
    Application.OnKey "{ENTER}", "EnterKey"
    Application.OnKey "{RIGHT}", "RightArrow"
    End Sub

    STANDARD MODULE
    Code:
    Dim Rng As Boolean
    
    Public Sub RightArrow()
    Application.EnableEvents = False
    RestrictCell
    If Rng = False Then ActiveCell.Offset(0, 1).Select
    Application.EnableEvents = True
    End Sub
    
    Public Sub EnterKey()
    Application.EnableEvents = False
    RestrictCell
    If Rng = False Then ActiveCell.Offset(1, 0).Select
    Application.EnableEvents = True
    End Sub
    
    Public Sub RestrictCell()
    Rng = False
    If ActiveCell.Cells.Count > 1 Then Exit Sub
    If Not Intersect(ActiveCell, Range("A1:D200")) Is Nothing Then
        ActiveCell.Offset(0, 1).Select
        Rng = True
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("E1:E199")) Is Nothing Then
        ActiveCell.Offset(1, -4).Select
        Rng = True
        GoTo Done
    End If
    If Not Intersect(ActiveCell, Range("E200")) Is Nothing Then
        ActiveCell.Offset(-200, -4).Select
        Rng = True
    End If
    Done:
    End Sub
    Last edited by Maudibe; 2013-10-20 at 16:20.

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud,

    It all comes down to what the OP really is after which is not entirely clear.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,643
    Thanks
    115
    Thanked 652 Times in 594 Posts
    RG,

    Sorry if my post is different from what you had responded to. I was revising it while you had posted yours. I should have placed it in a new post.

    Apologies

Posting Permissions

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