Deleting an unlocked merged cell on a protected worksheet 1

[Deniall]

I am developing a multi-sheet workbook for a building-industry client who wants to achieve limited "project management" capabilities entirely within Excel (Excel[ ]2010).[ ] Several of the worksheets have a top part that presents tabulated numerical and non-numerical data, the presentation of which is one of Excel's strengths.[ ] However the bottom of part of these worksheets is set up for the project manager to enter "free text" comments of unlimited extent, something that would be better achieved using a wordprocessor rather than a spreadsheet.

The number of columns and their various widths are dictated by the upper part.[ ] When I get to the bottom part I am forced to use merged cells and "wrap text" to allow the free text to flow across the total used-width of the sheet but no further, and to suggest to the user that they enter each paragraph into its own (merged) cell.

[Digression/confession.[ ] This whole endeavour sees me breaking two of my cardinal rules:[ ] (1)[ ]never use merged cells; and (2)[ ]when you want to process words use a word processor.[ ] But my client requires that the entire exercise be achieved within a spreadsheet, so…]

First problem was that the automatic adjustment to row heights that happens when you have "wrap text" applied to a cell does not work with merged cells.[ ] Luckily Prof Google found me a VBA-based solution for this, which I was able to modify so that it is activated via the Worksheet_Change event handler.

My next problem, and the reason for this post, is that I need the worksheet to be protected, with the few cells the user is allowed to modify set as unlocked.[ ] I have just discovered that with unlocked merged cells on a protected worksheet the {delete} key triggers an error to the effect that the cell is "protected and therefore read-only".[ ] I can change the contents of the cell, including removing all its characters, but I cannot use the {delete} key on the cell.[ ] I have no such problem with non-merged cells, nor if I unprotect the worksheet first.[ ] My first thought was that this was happening because some of the "now non-existent" cells in the merged grouping had been locked before the merge was done, but it turns out that the problem is more fundamental than this.

Has anyone encountered this problem and found a way around it?

Or is my only option to devise some complicated, convoluted approach involving using Application.OnKey to trap the use of the {delete} key, unprotect the worksheet, remove the contents of the cell, then re-protect the worksheet?

 

[combo]

Delete" key is equivalent to ClearContents method for Range object. Delete method tries to remove range, that implies shifting protected area, so the error. So if you need just to remove contents, use ClearContents instead.

combo

 

[Deniall]

Combo.[ ] It was the delete KEY that was giving me the trouble, not the delete METHOD. My users only know how to use the former.

Having spent many hours on this problem, I think I have finally stumbled across the cause.[ ] Whether or not you strike the problem seems to depend upon the order in which you format the group of cells that are to be merged.[ ] There are two formatting operations involved:[ ] unlocking and merging.[ ] If you unlock the component cells then merge them, you get the problem.[ ] But if you merge them and then unlock them you do NOT get it.[ ] I find this counterintuitive.[ ] I had meticulously unlocked then merged, which I now see leaves the merged cell in a sort-of "half locked" state.

You lives and you learns.[ ] Particularly where Excel is concerned.

 

[combo]

It seems that there's something wrong with merged cells, if you point to them in formula, you get an original range reference instead of single cell.

combo

 

[SkipVought]

Deniall, interesting conundrum. Cheers to your analytical approach and persistence. Thanks for sharing your discovery. I too attempt to avoid merged cells, but there are occasions. This is good stuff to have in the toolbox!

Also appreciate the excellent vignette providing the backdrop for this issue. It was clear, concise and complete. All these things are admirable in your approach, analysis, presentation of the problem and solution. Good job and success in developing a satisfactory product for your client!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]