Limiting focus range in Excel

[PeDa]

My Excel spreadsheet only allows the user to enter data in a narrow range of cells in one column (say B2...B10). If I press "cursor down" in the bottom cell (B10) the focus jumps back up to B2; if I press "cursor up" in the top cell (B2) it jumps to B10; this makes it difficult to scroll to the top or bottom of the list. How can I (using VBA I assume) halt the focus when it gets to the bottom or the top of the allowable range?

 

[SkipVought]

Hi,

Right click the Sheet Tab and select View Code]

Just above and in the left-hand codrner of the Code Window select Worksheet from the drop down.

Just above and in the right-hand codrner of the Code Window select Selection Change from the drop down.

This is probably where the code resides, unless it was done in the ThisWorkbook object.



Skip,

_{[red]Be Advised![/red] Coeds studying ancient Egyptian plumbing, might be known as...
Pharaoh Faucet Majors}

 

[PeDa]

Thank you Skip, but what I was really looking for was the code for the event, but have now developed the following. Not very elegant, but it works (AtTop and AtBottom are Public Boolean variables)
[tt]
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyTop As Integer, MyBottom As Integer
MyTop = 3
MyBottom = 10
If Target.Row = MyBottom And AtTop Then
Range("B" & Trim(Str(MyTop))).Select
Exit Sub
End If
If Target.Row = MyTop And AtBottom Then
Range("B" & Trim(Str(MyBottom))).Select
Exit Sub
End If
Select Case Target.Row
Case MyTop
AtTop = True
AtBottom = False
Case MyBottom
AtTop = False
AtBottom = True
Case Else
AtTop = False
AtBottom = False
End Select
End Sub
[/tt]

 

[vladk]

You did not declare AtTop and AtBottom

 

[vladk]

Oops. They are public. Sorry.

 

[DaveInIowa]

From your description, it appears as though you are accomplishing this by unlocking the cells you want to allow your user to edit and then protecting the sheet. The behavior you are looking for can be implemented with the following:

Private Sub Workbook_Open()
    ThisWorkbook.Worksheets("Sheet1").ScrollArea = "B3:B10"
End Sub

 

[SkipVought]

From you initial post, I thought that you inherited a sheet that already had this logic.

You don't really need ANY code at all.

Once you unlock cells and protect the sheet, TAB will get you there.

Skip,

_{[red]Be Advised![/red] A Last Will and Testament is...
A Dead Giveaway!}