0

I want to prevent the behavior where going past the last or first unlocked cell in a row wraps the selection to the next/previous row. This is the same behavior as described in this post.

The main difference between the post and my question is that I also want to limit which cells a user can select and I don't want to unlock all cells.

My reason:

The only way I can restrict a user from pasting over my formulas is by preventing them from selecting the cell. I know I can use VBA to accomplish this but the excel document I have is a macro free workbook and needs to remain as such.

Improve this question
8
  • FWIW, "wrapping" is what happens when the text is too long for the width of a cell and breaks into a new line in the same cell. Your choice of words is confusing.
    – teylyn
    Commented Feb 24, 2020 at 22:10
  • I know that's why I added the link to the other post. If you know what it should be called please let me know and I'll update my post. Term "Wrapping" as per the context of this question "The selection cursor moves from the last selectable cell in a row to the first selectable cell in the next row."
    – Mandelbrotter
    Commented Feb 24, 2020 at 22:14
  • The selection moves to the next row. I'm not sure I understand the issue. What does it do now and what would you like it to do instead?
    – teylyn
    Commented Feb 24, 2020 at 22:16
  • All but Cells A3:C6 are unlocked. I protect the sheet. I Select Cell C4. I use the left arrow key once. Currently: The selected cell is now A5. What I want: The selected cell does not move. In reverse Selecting A6 and using the right key moves it to C5 when I want it to not move.
    – Mandelbrotter
    Commented Feb 24, 2020 at 22:19
  • 'All but Cells A3:C6 are unlocked.' Double negative. Hard to understand. You mean you edited ALL cells in the sheet and set the Protection to unlocked??
    – teylyn
    Commented Feb 24, 2020 at 22:23

1 Answer 1

Reset to default
0

Format the cells to "locked" and protect the sheet. Unlock all the cells that you want to be editable. Leave the other cells at the default setting, which is "locked".

Then protect the sheet.

When you protect the sheet you can set an option that only unlocked cells can be selected.

In the animated screenshot, the yellow cells are unlocked. The arrow key cycles through the unlocked cells.

enter image description here

Edit after discussion in chat: lock cells with formulas, unlock data entry cells. Teach your users not to paste into data validation cells. Protect with allowing locked cells to be selected, then use Ctrl+arrow to navigate, but only press it after you know where you've ended up.

Improve this answer
5
  • That's what I've done and now this unwanted behavior has emerged. (Using the Left arrow key or tab on the last unlocked cell will move the selected cell to the next row)
    – Mandelbrotter
    Commented Feb 24, 2020 at 22:16
  • And what do you want to happen instead???? Why is that "undesirable"? If I have filled in all info in one row and use the arrow (btw, do you mean the other left, i.e. the right?), it's logical that the next available cell is selected, and that will be in the next row. What is the problem?
    – teylyn
    Commented Feb 24, 2020 at 22:18
  • I want to retain the behavior of an unprotected sheet that it does not move. Selecting A4 and moving left does not move the selected cursor to XFD3. Reason: End users are complaining that this behavior is not standard or normal. If it is impossible to acomplish, that is ok and I'll add a bunch of unlocked buffer columns before and after, to give my users room to over shoot.
    – Mandelbrotter
    Commented Feb 24, 2020 at 22:25
  • I added a screenshot. The next unlocked cell in the direction of the arrow key will be selected. Not XFD because only unlocked cells can be selected.
    – teylyn
    Commented Feb 24, 2020 at 22:32
  • answer updated after chat
    – teylyn
    Commented Feb 24, 2020 at 22:59

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .