Prevent Copy/paste ruining data validation in shared workbook

Hi Experts,

I need to some help with a macro formula. I have Excel 2003 and have set up data validation rules to restrict entries users can make.

However, I noticed that users could still copy/paste over the cells with incorrect data that data validation tool did not pick up. I had a dig around on the net and found a bit of VBA code that could correct the issue. The VBA code is outlined below.

It works perfectly when the workbook is unshared but as soon as I share it the macro ceases to execute giving rise to the same issue as before. Unfortunately I require 20 users to acess the worksheet at various times so I must share it.

Can anyone suggest a way round this problem

Thanks,

Owen

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", vbCritical
End If
End Sub

Private Function HasValidation(r) As Boolean
' Returns True if every cell in Range r uses Data Validation
On Error Resume Next
x = r.Validation.Type
If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

i am also intersted in this.
My thoughts were to disable normal paste (via Ctrl-V, right click shortcut, or Paste button) when this workbook is activated and replace with a paste special values function, then revert to normal when not active workbook.
But dont know how to do it.
As your suggested solution doesnt allow for the possibility the validation replaced (pasted in from source workbook), that differs from your destination validation rule does it ?
Interested in suggested solutions all the same.