Home key misbehavior

W

Wild Bill

Board Regular
Joined
Feb 20, 2006
Messages
125
I have a spreadsheet where I have unlocked the cells in the user input area and turned on sheet protection. Rows and columns outside the user input area are hidden and their cells are locked. Frozen panes is turned on with cell B3 as the upper left.

Ctrl-home works fine; it takes me to B3.

When sheet protection is on, the home key doesn't behave right. Rather than moving the cursor to column B in the current row, it jumps to row 1 in the first hidden column.

When I turn protection off it works as expected; the cursor moves to column B.

Another strange behavior I'm encountering when protection is on is that when I use arrow keys to move the cursor to the right and I hit the end of the user input area the cursor jumps to the first column of the next row. With protection off, the cursor hits the end of the row and just stops. Right arrow does nothing at that point. Similarly, when I use down arrow and hit the bottom of the user area the cursor jumps to the top of the next column. With protection off, the cursor stops at the bottom of the column and does nothing in response to more down arrow inputs.

Transition navigation keys is not selected.

What is causing this behavior? Is there an option or setting somewhere that I'm missing? I haven't seen this happen before.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
If you want to restrict the user to only be able to click and scroll a particular range on a worksheet, set this range as the ScrollArea property of the worksheet:
rightclick the sheet-tab, click View Code, press F4, enter the Range in the ScrollArea line, e.g.: "A1:D20"
 
Upvote 0
That's a feature I wasn't aware of, thanks for pointing it out. It wasn't the solution to my problem but the suggestion did prompt me to poke around some more and I believe I found the solution.

ScrollArea wasn't the solution because users of my spreadsheet need to be able to insert new rows, which would require the ScrollArea to be expanded. If there's a way to actively expand the ScrollArea in response to the user's insertion of a new row, I haven't found it.

In order to restrict users to a specified range I have unlocked the cells in the user range, leaving all other cells locked, and turned on sheet protection.

I need protection on in order to place some restrictions on the user. For instance, inserting or deleting columns cannot be allowed as it would invalidate lots of VBA code triggered off worksheet changes.

I found that my problem was tied to the protection option to prevent users from selecting locked cells. When I checked the box to allow selection of locked cells, the strange behavior ceased. So my users can select but not change locked cells. Most of the locked cells are hidden, so users poking around in the few unhidden locked cells isn't much of a problem.
 
Upvote 0
I have the same problem with Excel 2013.

When I press the Home key on a sheet with some locked cells, it doesn't take the cursor to the first unlocked cell at the start of that row, it moves it to the first unlocked cell at the top of the sheet, and users have to spend a bit of time scrolling back down the sheet to where they made their last entry.

This problem disappears if I allow users to select (but not edit) locked cells, but then they continuously get the warning message:
"The cell or chart you are trying to change is on a protected sheet"
every time the accidentally try and type into one of the locked cells.
This gets quite annoying very fast...


Wild Bill said:
That's a feature I wasn't aware of, thanks for pointing it out. It wasn't the solution to my problem but the suggestion did prompt me to poke around some more and I believe I found the solution.

ScrollArea wasn't the solution because users of my spreadsheet need to be able to insert new rows, which would require the ScrollArea to be expanded. If there's a way to actively expand the ScrollArea in response to the user's insertion of a new row, I haven't found it.

In order to restrict users to a specified range I have unlocked the cells in the user range, leaving all other cells locked, and turned on sheet protection.

I need protection on in order to place some restrictions on the user. For instance, inserting or deleting columns cannot be allowed as it would invalidate lots of VBA code triggered off worksheet changes.

I found that my problem was tied to the protection option to prevent users from selecting locked cells. When I checked the box to allow selection of locked cells, the strange behavior ceased. So my users can select but not change locked cells. Most of the locked cells are hidden, so users poking around in the few unhidden locked cells isn't much of a problem.
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
SMALL function returning 2 values
2
Replies
10
Views
379
Joe4
Joe4
S
  • Question
Excel/VBA does to wrong cell using keyboard arrow keys
Replies
3
Views
568
StephenCrump
S
S
  • Solved
Protected Sheet crash when use the tab key
2
Replies
10
Views
685
Sergio
S
Dan Wilson
  • Solved
Question
Replies
3
Views
199
Peter_SSs
Peter_SSs
A
  • Question
After User Enters A Value (Validation List), Not Automatically Advancing To Next Cell As Per "Select"
Replies
1
Views
173
Ark68
A

Forum statistics

Threads
1,214,827
Messages
6,121,821
Members
449,049
Latest member
cybersurfer5000

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top