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
Next ws
End Sub
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?
Hi Dick
2007 and 2010 also have this bug
I report that bug in the 2003 beta and in the 2007 beta and I
Not do it again in the 2010 beta.
I saw this in 2003, and it’s still there in 2007. I was quite annoyed too.
This bug is something I come across quite frequently in 2003 if “select locked cells” is unchecked, but if “select unlocked cells” is checked. If neither or both of these settings are enabled, the bug appears to go away.
Occasionally, I’m asked to use a sheet purely to display data – in situations where it’s important for users not to be able to select locked cells, but where having unlocked/selectable cells is not a requirement, I find that by making sure both the “select locked cells” and “select unlocked cells” boxes are unchecked, I can still use the CTRL+PAGE UP/PAGE DOWN shortcut keys. Therefore, the bug would appear to be related to both the “select locked cells” and “select unlocked cells” settings.
Very strange… maybe there’s some legacy code buried within Excel that uses these keystrokes for navigation? Just a thought… and a completely unqualified guess of a thought at that!
Once I was asked to set up a situation where, under certain circumstances, sheets would be programically hidden / unhidden (no problem), programatically written to (no problem), and prevent users from writing to certain areas that the program could itself write to. I became very aware of the limitations and bugs surrounding “Select Locked Cells” and “Select Unlocked Cells”. Try to change those checkboxes programatically. The only workaround I could manage was using SendKeys (ack!). In my opinion this whole Locked / Unlocked thing could use an overhaul by Microsoft.