I think your best approach is to turn on worksheet protection with the
UserinterfaceOnly parameter set to true.

For example, protect Sheet1 manually. If A1 is locked and you run this code
it will fail of course:

Sub CopyCell()
Range("C1").Copy Range("A1")
End Sub

But first run this code and then try it:

Sub UserIntefaceOnly()
Sheet1.Protect , , , , True
End Sub


The only catch is that UserInterfaceOnly setting is not saved with a
workbook. So when it is re-opened it has to be set again by macro. You
should use your Auto_Open or Workbook_Open code to do this.

--
Jim
"Marie J-son" wrote in message
...
| Hi,
|
| I have a worksheet I need to have protected, and when I run code to
restore
| cell format, I unprotect it temporary. However, this causes clipboard to
| turn empty and since I run the code both at worksheet_activate and
| worksheet_change events, the result is that you can't copy-paste between
| sheets.
|
| At line below, the clipboard turn empty ...
| "Application.Worksheets(XshtName).UnProtect Password:=sPass"
|
| 1/ Is there a way to work around this and still use the code both at
| worksheet_change and worksheet_activate?
| 2/ The reason why I have it on worksheet_activate is because I want to
| restore cell formats when drag and drop occure from other sheets - - it
| doesn't trig the worksheet_change event, I'm told. Right? Workaround?
|
|
| /Kind regards
|
|