Merged cells withing Protected Sheets

[Ann]

I have a range of cells that we merged and then unlocked. When we protect
the sheet and say the user can only go to the unloced cells. We can tab thru
until we get to the cell that created from a group of cells merged. it will
then tab to the next cell and just keep going back and forth between those
two forever. Any suggestions?

 

[BoniM]

You must have merged rows and have other unlocked cells on the same row...
It confuses tab because the merged range is not just the upper left cell,
but ALL of the cells in one.
You can use a merged range if there are no other unlocked cells on the same
row as one of the rows that have been merged.

 

[BoniM]

Received email request for further explanation (from lurker) sharing here as
well:

Say cells A1 and B3 are unlocked and the range D24 has been merged and
also has the locked property turned off. Protecting the sheet and unchecking
select locked cells, allows the user to only select unlocked cells… which
means tab will move them to the next unlocked cell on the current row or, if
there are no more on this row, the first unlocked cell on the next row. With
the cells listed above, a tab from A1 will move you to D2, a tab from D2 will
move you to B3, and a tab from B3 will move you to D3… part of the merged
range – but still technically the next unlocked cell. Of course, once the
selection gets there, it’s really back in cell D2 - because of the merge -
and a tab from cell D2 will move the selection to B3 and then you’re stuck in
a loop and it never makes it back to A1. (Though arrow keys and the mouse
will still get you out…)

Just one more reason to avoid merging cells!

 

[Gord Dibben]

Un-merge the cells.

You have run into just one of a great host of problems created by merged cells.


Gord Dibben MS Excel MVP