If the deactivate event is in the DataSheet code module, try this:

Private Sub Worksheet_Deactivate()
On Error Resume Next
Set sh = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("datasheet")
.Unprotect
Worksheets("FormatSheet") _
.Range("entiresheet").Copy
.Activate
.Range("A1") _
.PasteSpecial Paste:=xlPasteFormats
.Range("A1").Select
.Protect
End With
sh.Activate
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy


"Vicar" wrote in message
...
I have a protected worksheet which users can enter data into the
unprotected
areas. To try to prevent these areas becoming corrupted by formats being
copied in from other worksheets, I have added the following deactivate
change
event to "datasheet" (tried at first to use a cell change event, but it
became too complex):

Private Sub Worksheet_Deactivate()
Dim mynewSheet As String

Application.EnableEvents = False
Application.ScreenUpdating = False
mynewSheet = ActiveSheet.Name

Worksheets("formatsheet").Activate 'very hidden copy of datasheet
formats
Application.Goto reference:="entiresheet"
Selection.Copy
Worksheets("datasheet").Activate
ActiveSheet.Unprotect
Range("a1").Select
Selection.PasteSpecial Paste:=xlPasteFormats
Range("a1").Select
ActiveSheet.Protect DrawingObjects:=True

Worksheets(mynewSheet).Activate

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

This works fine when datasheet is unprotected and without the
Activesheet.Unprotect/Protect statements, but the PasteSpecial fails when
the
sheet is protected and with these statements included as above. Can anyone
tell me why?