use source formatting with protected worksheet

[NDORengineer]

I have created a spreadsheet with multiple complex formulas. I protected the
worksheet , but allowed the end user to copy and paste protected cells into
unprotected cells. When I do this the source protected cells are no longer
protected, its useing the destination formating not the source formatting.
Is there anyway around this?

 

[Gord Dibben]

When a sheet is protected you cannot change formatting of the destination
cells simply by pasting source cells that are locked.

You would have to use event code to unprotect, paste cells with formatting
then re-protect

Something like this which is slow on large selections.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Selection
rng1.Copy
ActiveSheet.Unprotect
rng1.Copy Destination:=ActiveCell
ActiveSheet.Protect
End Sub


Gord Dibben MS Excel MVP

 

[Gord Dibben]

And the reason it runs slow is because I forgot to disable events.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng1 As Range
Set rng1 = Selection
rng1.Copy
ActiveSheet.Unprotect
Application.EnableEvents = False
rng1.Copy Destination:=ActiveCell
ActiveSheet.Protect
Application.EnableEvents = True
End Sub


Gord