How to control formatting of unprotected cells in a protected sheet? Ideas?

[Dman333]

To all,

I have a model that is protected with sheets that have protected cells together with unprotected cells for inputs. The input cells have different backgrounds, font colors, and some with conditional formatting. The question is how do you prevent someone from accidentally erasing of overwriting the desired format or conditional formatting if they do a drag down or copy/paste into that cell. Classic example would be a drag down (the inputs are lined up in a column) where the inside boarder is a dash and the outside is a solid line. If you do that from the top, to top solid line becomes the top of each cell below it that is subject to the drag down.

I'm a novice at Vba, so I'm wondering if there's a way to further block what formatting can, or can't, be done on unprotected cells in a protected workbook with code?

Anyone?

Thank you in advance for your time - Much appreciated.

Cheers.
Dman333

 

[6StringJazzer]

I haven't solved this exact problem but it can't be done with normal protection and formatting. This, IMHO, is a serious flaw with how sheet protection works. I am amazed that after this many years, Microsoft hasn't added an option to allow data input but protect formatting and data validation.

I think this could be done with VBA. Here's how I would do it. I would take my model sheet, and make a copy of it. Then make the copy VeryHidden. Every time data in a cell is changed in the visible sheet, it will trigger a Worksheet_Change event which will copy the formatting for that cell from the VeryHidden sheet to the visible sheet.

VBA can't prevent formatting changes to a cell, because it doesn't know when they happen. Unfortunately there is no event to detect a change in formatting. So the update above would occur only upon user input of data.

 

[Dman333]

I haven't solved this exact problem but it can't be done with normal protection and formatting. This, IMHO, is a serious flaw with how sheet protection works. I am amazed that after this many years, Microsoft hasn't added an option to allow data input but protect formatting and data validation.

I think this could be done with VBA. Here's how I would do it. I would take my model sheet, and make a copy of it. Then make the copy VeryHidden. Every time data in a cell is changed in the visible sheet, it will trigger a Worksheet_Change event which will copy the formatting for that cell from the VeryHidden sheet to the visible sheet.

VBA can't prevent formatting changes to a cell, because it doesn't know when they happen. Unfortunately there is no event to detect a change in formatting. So the update above would occur only upon user input of data.

I was afraid there wouldn't be an easy solution to this. Unfortunately, the file has a tone of calculations and I set it up specifically so it would be quick and not need to go to manual calc. It's also setup so tabs can be scaled up or down and the consolidation still works. Unfortunately, I think I have to put up with the glitch and let users re-format the cells if they want it back the way it was originally.

Having said that - this is a really creative way to get it done and thanks a ton for your time and confirming the issue. Maybe someone else has a different idea?

Thanks again.
Best,
DMan333