Locking formatting

[Thomas Hardy]

Does anyone know how to lock the formatting, validation rules and
conditional formatting in a cell without locking the data?

I have a data entry template and everything works fine unless a user copies
an unlocked cell and pastes it into another unlocked cell, then the
formatting and validation rules are taken along as well.

I can stop this for myself by using Paste Values, but I can not see how to
only allow the pasting of values for other users of the spreadsheet.

I have searched everywhere in the Help files and have the feeling that this
is just not possible using standard Excel functionality (i.e. no custom
scripting), but maybe somebody out there knows better...

Thanks for your help

Thomas

 

[Guest]

Let's say you want to allow the user to change the value in cell A1, but not
its format.

In cell A1 enter something like =Z100. Then setup the desired format for
A1. Then protect A1 in the usual manner.

The user will be able to change the value in A1 by changing the value in
Z100, but will not be able to change its format.

 

[Joerg]

You are right: Not possible. Even scripting doesn't help much since there is
nothing like a BeforePaste or AfterCopy event, which would allow to
intervene. The only option I see is to use scripting and (re)set all desired
formats after a SheetChange event or latest before saving. This can involve
a lot of code...

Joerg

 

[Thomas Hardy]

Thanks Joerg,

Just as I feared.

I think this is quite a gap in Excel protection functionality but there is
nothing I can do to change that. I'll just have to include a warning in teh
documentation. Thanks again

Thomas

 

[Dave Peterson]

You could try what Joerg suggested.

I created another worksheet (called Hidden) that had mirrored all the formats of
Sheet1. Then each time the user changed a value, the formats were copied from
that hidden worksheet and pasted over the changed range.

You could do the same kind of thing before the user saves--to get those
formatting changes the user did without changing the values.

This is the code I used behind the worksheet:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Worksheets("Hidden").Range(Target.Address).Copy
Target.PasteSpecial Paste:=xlPasteFormats
Application.EnableEvents = True
End Sub

You may find that it's not worth doing--especially if you rely on Edit|Undo!

You may have to unprotect the sheet, do the work and reprotect the sheet, too.
(I didn't protect the sheet in my test.)

 

[Debra Dalgleish]

You could check the CutCopyMode, and ask if the user wants to paste. It
wouldn't prevent invalid entries being pasted into cells with data
validation, but would preserve the formatting. For example:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rsp As Long
If Application.CutCopyMode <> False Then
rsp = MsgBox("Paste?", vbQuestion + vbYesNo, "Paste?")
If rsp = vbYes Then
Target.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Else
Application.CutCopyMode = False
End If
End If
End Sub

 

[Thomas Hardy]

Thanks to you and Dave for your suggestions, I'll try them both and pick a
winner ;-)

Thomas

 

[Joerg]

That's a good approach - however only as long as Thomas' users never
drag&drop (=cut) or use the small cell handle to copy over a range....
I personally would go for the restore approach. This also allows you te
reset your formulas which the users might have turned useless through their
*cutting* and pasting.

Joerg