| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Edit text format in non-protected cells in protected worksheet | Excel Discussion (Misc queries) | |||
| Preventing formatting changes in a protected worksheet | Excel Discussion (Misc queries) | |||
| Linking to Protected Source File on Shared Server | Setting up and Configuration of Excel | |||
| How can I have formatting toolbar active for the unprotected(unlocked) cells of the protected worksheet??? | Excel Discussion (Misc queries) | |||
| Change Source of Link in Protected Sheet | Excel Discussion (Misc queries) | |||
Linear Mode
