Stop Excel 'wraparound' scrolling

[Jehannum_2000]

The first 7 rows of the spreadsheet are fixed in place (with Freeze Pane, if I remember rightly).

If the user is on row 8 and presses the up arrow key where there is a locked, unselectable cell above, Excel scrolls to row 65 (the last row that allows data entry).

This is annoying for the user. There is not usually any data this far down, so they just see blank rows.

Instead, I want the active cell simply to stay at row 8, i.e. for there to be no scrolling down to row 65.

I don't want the user to have to use Scroll Lock (which may or may not be on their keyboard). I want Excel or VBA to enforce the above behaviour.

I'd be grateful for a solution.

 

[6StringJazzer]

When the sheet is protected and "Select locked cells" is unchecked (you didn't explicitly mention these but I assume that's the case), Excel will cycle through the unlocked cells as the user navigates using Tab or arrow keys. (The fact that Freeze Panes is being used is a red herring.) That's all by design.

This solution is for a specific worksheet. If you need a more general solution please describe in more detail what you need.

Create a new standard module and add this code:

Public Sub PreventWrap()

   If Not Selection.Offset(-1, 0).Locked Then
      Selection.Offset(-1, 0).Select
   End If

End Sub

In the code module for the worksheet with the data add this code:

Private Sub Worksheet_Activate()
   Application.OnKey "{Up}", "PreventWrap"
End Sub

Private Sub Worksheet_Deactivate()
   Application.OnKey "{Up}"
End Sub

In the module for ThisWorkbook add this code; change "Wrap Example" to your actual sheet name:

Private Sub Workbook_Open()
   If ActiveSheet.Name = "Wrap Example" Then
      Application.OnKey "{Up}", "PreventWrap"
   Else
      Application.OnKey "{Up}"
   End If
End Sub
 
Private Sub Workbook_Activate()
   If ActiveSheet.Name = "Wrap Example" Then
      Application.OnKey "{Up}", "PreventWrap"
   Else
      Application.OnKey "{Up}"
   End If
End Sub
 
Private Sub Workbook_Deactivate()
   Application.OnKey "{Up}"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Application.OnKey "{Up}"
End Sub

 

[Jehannum_2000]

Thank you.

Pity it's not just a simple option though!