MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by Maurice G on November 07, 2001 5:38 AM

when entering information on a worksheet that already has a defined number of horizontal and vertical cells, is there any way of ensuring that the verticle and horizontal cursors movement just encapsulates the information that you are working on without moving outside this area?

Posted by Damon Ostrander on November 08, 2001 11:16 PM

Hi Maurice,

Here is some code that, when placed in the worksheet's event code area, will do this. Just set the MaxRows and MaxCols constants to the values you want. To put the code in the event code area, right-click on the worksheet's tab, select View Code, then paste the code into the VBE code pane that appears.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const MaxRows = 19
Const MaxCols = 8
Application.EnableEvents = False
If Target.Row > MaxRows Then Cells(MaxRows, Target.Column).Select
If Target.Column > MaxCols Then Cells(Selection.Row, MaxCols).Select
Application.EnableEvents = True
End Sub

Happy computing