Double clicking protected cell deletes entire contents

[oenleunc]

In a spreadsheet I've built, I have cells that are locked with formulas hidden. When a user tries to edit the contents of the cell, instead of being able to place the curser when the cell is doubleclicked, the contents clear and the user has to retype everything over again. I know this is because of the hidden aspect of the protection, but I have a few formulas here and there that I don't visibile. Is there a solution to the problem? I've been getting some complaints from coworkers who have to retype cell contents. Thanks!

[jeffreybrown]

If information is being cleared, there must be a macro doing it. On the sheet tab in question, can you post the macro for your DoubleClick event?

[oenleunc]

I don't have a doubleclick event. It's doing it on every sheet and every (unlocked) cell. It doesn't clear the cell contents when the sheet is unprotected. I've encountered this problem on some other protected spreadsheets, and with some research I've been able to determine that it's the hidden portion of protection that's doing it, but I've been unable to find a solution that doesn't include unhiding the formulas.

[jeffreybrown]

This doesn't sound like anything I've ever encountered.

When you say protected sheets, are these sheets protected manually or via code?

Do you have a sample you can post with this happening?

[oenleunc]

It's protected with a macro (I need to unprotect and reprotect often), but when I protect manually it does the same thing.

I've unlocked the tab labeled "Alan" so you can see what it does when unprotected. I've also unlocked the code in the developer tab.

[jeffreybrown]

I went to the Alan tab and manually protected the sheet but did not see anything unusual happen.

Is there something specific I can look for? Also, are you sure there is nothing in the code that could be causing this? You VBA project is locked.

[oenleunc]

Try typing into the coments column of the table, click away from it, and then try double clicking in the cell and editing it. I protected it manually and it still did the same. I unlocked the VBA (for real this time) so you should be able to see my macros. Don't laugh at them, they work

[jeffreybrown]

Ok what I see, on the comments cell in the table (G7), uncheck the Hidden checkbox under the format cells Protection tab.

[oenleunc]

That's what I figured the problem was. I was hoping there was a fix that didn't involve unhiding. It isn't necessarily important for the cells that will really need the editing in this spreadsheet I suppose, since there aren't any formulas in those cells. I can think of other instances where it would be handy to have the Hidden protection on and still be able to edit.

[jeffreybrown]

Exactly, that is the way I see it. With protection on, you are just hiding formulas so why mark data entry cells as hidden. Sounds like your coworkers have identified an error that needs fixed.

Also, I don't really understand why you would hide a formula from view. What if they at least wanted to trace the cells to see what is being calculated. Doesn't mean they can change the formula as it is locked, but you can use them as another opportunity to catch any accidental errors in your formula.

[oenleunc]

They don't need to understand what's happening and how. Spreadsheets should be magical

[jeffreybrown]

Your the builder so you know best, although as I stated before, they did point out their data was disappearing