In Excel 2003, I was happily moving from sheet to sheet using Control+Page Up/Page Down. Suddenly it stopped working. Instead of moving to the next sheet, I was scrolling one page to the left/right with those key combinations. I couldn’t get off of this particular tab without clicking on another one with the mouse.
I pressed the Scroll Lock button a few times, but that had no effect. The sheet had frozen panes, so unfroze them (Windows – Unfreeze Panes) and even removed the pane dividers by dragging them to the right and top of the window. None of this mattered, I still couldn’t use Control+Page Up/Page Down to get to the next sheet.
Finally, I removed the worksheet protection and the shortcut key was restored. I have lots of protected worksheets, so why have I not seen this behavior before. Is there an option to disable this function, and why would there be?
It seems that if “select locked cells” is unchecked, meaning the user cannot even select cells that can’t be changed, then Excel exhibits this behavior. Allowing selection of locked cells returns the shortcut to (what I consider) normal behavior. I used the following macro to change that property for every worksheet. I didn’t even have to unprotect the sheets, which seems odd.
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.EnableSelection = xlNoRestrictions
Is this one of those things that everyone has seen before except me? Can someone give it a whirl in 2007 and 2010 and comment?