Protect excel from formatting while coping and pasting

[babaso_tawase]

In protected excel , in unprotected cells user should enter value by manually or copy and paste, but while coping from another source and pasting in unprotected cell, format changes as that of source data, even though formatting option not selected while protecting sheet....formatting should be not change....

 

[liveinhope]

you're right ! I don't have an actual solution but if you use paste special instead of paste then select value only you end up with the required format

 

[babaso_tawase]

Thanks for reply , paste special is solution, but all members not doing same that's main problem...so want solution for that

 

[liveinhope]

unfortunately that's the way "normal" copy and paste works . it's a bug or feature of excel - personally I see it as a bug but others many not agree

(format isn't the only thing that "normal copy and paste" does that might not be what you want . eg a normal copy-and-paste into a cell it ends up with the data validation that he "copy from" cell had.
If the "copy from" cell didn't have any validation you cell ends up with no validation so user can accidentally or deliberately bypass any validations you set up)

real solution is beyond my expertise ; hope another user can help here

I'm using 2007 ... what version are you on


...

 

[FDibbins]

if you uncheck the "select locked" option when you protect the sheet, users will not be able to select anything to copy in the 1st place

 

[liveinhope]

that's a good tip FDibbins will at least stop users copying from a "locked" cell on the same worksheet , whether done accidentally or deliberately ! I will use this tip in the near future

As I understand it there is no way to stop someone using "normal" copy-paste ... if the cell they copy from is unlocked or on another worksheet you will still lose the format (and any validations that may exist) - - is that how you see it too

 

[babaso_tawase]

if you uncheck the "select locked" option when you protect the sheet, users will not be able to select anything to copy in the 1st place

Thanks for reply, this solution good, but when data to be copy from other excel data source then not works...