One more finding:

Even I can unprotect the worksheet, the "With Validation" cannot be executed
in the shared workbook.

So I think I need to be able to control the sharing inside the script.

Much appreciate for your any idea!!

Florence

"Norman Jones" wrote:

Hi Florence,

See Limitations of shared workbooks' in Excel help.

Specifically, this article indicates that shared workbooks will not allow
the application or removal of sheet protection passwords.

---
Regards,
Norman



"Florence" wrote in message
...
Dear all,

I have the following scripts that assign value & validate for specific
column:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 20 Then
Sheet1.Unprotect "password"
If Len(Target.Value) = 0 Then
With Target(1, 0)
.Value = Cells(Target.Row, Target.Column - 7)
With .Validation
..........
End With
End With
End If
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowSorting:=True, AllowFiltering:=True,
AllowUsingPivotTables:= _
True
Sheet1.Protect "password", , , , , , , , , , , , , True, True, True
End If

It's working properly in exclusive mode but failed when sharing the
workbook
(Set it under the "Tools" manual).

Much appreciate for your any idea!!

Thanks in advance!

Florence