use source formatting with protected worksheet
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? |
use source formatting with protected worksheet
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 On Tue, 22 Jul 2008 05:33:00 -0700, NDORengineer wrote: 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? |
use source formatting with protected worksheet
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 On Tue, 22 Jul 2008 09:42:15 -0700, Gord Dibben <gorddibbATshawDOTca wrote: 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 On Tue, 22 Jul 2008 05:33:00 -0700, NDORengineer wrote: 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? |
All times are GMT +1. The time now is 06:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com