Software

Ask something
Discussion Locked

restricting movement in Excel

0 helpful
3 replies

by blauber

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
Is there a way to restrict where the active cell moves to in Excel? I have a spreadsheet and wants to make it as simple as possible for users. When they type in the first open blank cell and hit return, I want it to next move to a specific cell on the page. In other words the user will not have to use arrow keys, since the active cell is restricted to only those cells that I want the user to type in.

3 replies

by dklippert

First of all, select the cells that you wish to be changed. Choose Format>Cells Protection and remove the check sign from the Locked box.
Now go to Tools>Protection and choose to Protect the Worksheet.
At this point the user can only change the cells you unlocked. The Tab key will move them from unlocked cell to unlocked cell. We're still faced with the pesky Enter key. Here's the solution from John Walkenback:
http://j-walk.com/ss/excel/usertips/tip002.htm

"To create a simple macro that turns this setting on when the workbook is opened, press Alt-F11 to activate the Visual Basic Editor. Locate your workbook name in the Project window, and double-click it to expand its listing. Then double-click the item labeled ThisWorkbook and enter the following VBA code:

Private Sub Workbook_Open()
Worksheets("Sheet1").EnableSelection = xlUnlockedCells
End Sub

This macro executes whenever the workbook is opened, and sets the EnableSelection property of Sheet1 to xlUnlockedCells."

by blauber

Exactly what I neeeded! I'm learning that there are a lot of amazing features hidden just below the surface.

This discussion is closed to new replies.