Copy/paste loses cell formatting and data validation

[petero]

This is a well-known problem though I couldn't find a solution here or anywhere. When a protected sheet has cells which are not locked, but have formatting and data validation, a simple copy/paste into those cells from another sheet loses the formatting and validation in the target cells. Anyone know why this is, and if there's a workaroud?

On its face, ths issue seems like its a major drawback for anyone who builds excel models for others. Why there isn't a way to permanently lock the formatting/data validation is a mystery. Anyone help?

 

[baitmaster]

you could add the formatting to another cell, then paste it automatically using the worksheet_change event. Not ideal, but ensures the formatting stays - but you do lose the undo option

 

[Datsmart]

If you want to retain Formatting and Validation do not copy/paste, use copy/pastevalues.

 

[petero]

If you've developed excel applications for 1000 users, you'd know that's not a practical solution.

Losing undo is obviously not optimal either. WHY doesn't MSFT address this???

 

[baitmaster]

what about resetting the formats on worksheet close? the only person affected by the format loss is the person that pasted, so preseumeably they don't need to worry as they've already done what they need to do, and Undo isn't an issue either?

Just create a "formats" routine, and add whatever you want, running before close, this corrects the whole model...

 

[baitmaster]

and please don't suggest that MS perfect everything, I make a decent living out of the fact that Excel confuses the hell out of everyone I work with, thank you very much

 

[petero]

Formatting at close is an interesting idea but misses the data validation issue entirely. If I build a spreadsheet model where the user in a particular cell on a PROTECTED sheet can only put in an integer between 1 and 10, they can type "cat" in a cell of another sheet and paste that into my properly data validated cell. Formatting at close does not address that problem at all - and its a BIG one.

I honestly just don't get how excel doesn't support this somehow. It would basically be some sort of protected cell/sheet option switch that only allowed "paste/special/values" for pasting when the sheet is protected - and DATA VALIDATION AND FORMATTING is PRESERVED (apologies for the caps, this is just too irritating for lowercase)