MrExcel Publishing
Your One Stop for Excel Tips & Solutions

MOVING AROUND IN A SPREADSHEET W/LOCKED CELLS


Posted by DALE on April 25, 2001 6:04 PM

I CREATED A SPREADSHEET WITH ONLY A FEW CEELS UNLOCKED. I WOULD LIKE TO MOVE TO THE UNLOCKED CELLS ONLY, MOVING DOWN IN THE COLUMN. THE TAB KEY MOVES ACROSS IN ROWS AN THE ENTER KEY STOPS AT EVERY CELL. IS THERE A KEY THAT MOVES IN COLUMNS TO THE UNLOCKED CELLS?
THANK YOU
DALE


Posted by Mark W. on April 25, 2001 6:12 PM

Say, your unlocked cells are in columns A:C. You
can Control click on column A, then B, then C. Now
the tab key will only travel down the columns.

Posted by Dave Hawley on April 25, 2001 6:33 PM


Hi Dale

The best way to do this is with VBA. Right click on your sheet name tab, select "View Code" and paste in this code:

Private Sub Worksheet_Activate()
Me.EnableSelection = xlUnlockedCells
End Sub

Now push Alt+Q and save. Select any other sheet. Now whenver you Activate you sheet you will only be able select Unlocked cells.

To set it back to normal simply use:
Me.EnableSelection = xlNoRestrictions


Dave

OzGrid Business Applications

Posted by Kevin James on April 25, 2001 6:41 PM

Dale:

If you'd like to do what you are trying to do with VBA, you only missed one step: You need to protect your worksheet. After that, only the unlocked cells can be tabbed to.

From the menu bar: Tools / Protection / Protect Sheet.

And by the way, Whow, unlock that caplock. In the cyberworld, that's akin to SHOUTING!!

Have a great day. :)

Posted by Kevin James on April 25, 2001 6:41 PM

Dale:

If you'd like to do what you are trying to do without VBA, you only missed one step: You need to protect your worksheet. After that, only the unlocked cells can be tabbed to.

From the menu bar: Tools / Protection / Protect Sheet.

And by the way, Whow, unlock that caplock. In the cyberworld, that's akin to SHOUTING!!

Have a great day. :)