Hi all,

I have a problem of adding validation to unlocked cells in a protected
sheet.

On Workbook_Open(), the following protection is made:

For Each wks In InputSheets
wks.EnableSelection = xlUnlockedCells
wks.Protect Password:="12345", Contents:=True, _
DrawingObjects:=True, userinterfaceonly:=True
Next

On the Worsheet_SelectionChange() event of the protected sheet, the
following validation is performed on the _unlocked_ range:

With UnlockedCell.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=" & ListSource
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With

However, whenever a cell in the unlocked range is selected, the debugger
turned on, highlighted the line ".Add Type = .... " and the error is
"Application-defined or object defined error". I am baffled because

1. The range in question is _unlocked_ one.
2. I have set the UserInterfaceOnly argument in the Protect method to true.

Anybody has ideas on what's happened? Thanks for your advice in advance.

Frederick Chow
However,

What is "ListSource" ?
A variable or named range ?

tim


--
Tim Williams
Palo Alto, CA


"Frederick Chow" wrote in message
...
Hi all,

I have a problem of adding validation to unlocked cells in a protected
sheet.

On Workbook_Open(), the following protection is made:

For Each wks In InputSheets
wks.EnableSelection = xlUnlockedCells
wks.Protect Password:="12345", Contents:=True, _
DrawingObjects:=True, userinterfaceonly:=True
Next

On the Worsheet_SelectionChange() event of the protected sheet, the
following validation is performed on the _unlocked_ range:

With UnlockedCell.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=" & ListSource
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With

However, whenever a cell in the unlocked range is selected, the debugger
turned on, highlighted the line ".Add Type = .... " and the error is
"Application-defined or object defined error". I am baffled because

1. The range in question is _unlocked_ one.
2. I have set the UserInterfaceOnly argument in the Protect method to
true.

Anybody has ideas on what's happened? Thanks for your advice in advance.

Frederick Chow
However,

It is a named range. No problem when the worksheet is not protected.

"Tim Williams" <timjwilliams at gmail dot com wrote in message
...
What is "ListSource" ?
A variable or named range ?

tim


--
Tim Williams
Palo Alto, CA


"Frederick Chow" wrote in message
...
Hi all,

I have a problem of adding validation to unlocked cells in a protected
sheet.

On Workbook_Open(), the following protection is made:

For Each wks In InputSheets
wks.EnableSelection = xlUnlockedCells
wks.Protect Password:="12345", Contents:=True, _
DrawingObjects:=True, userinterfaceonly:=True
Next

On the Worsheet_SelectionChange() event of the protected sheet, the
following validation is performed on the _unlocked_ range:

With UnlockedCell.Validation
.Delete
.Add Type:=xlValidateList, Formula1:="=" & ListSource
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With

However, whenever a cell in the unlocked range is selected, the debugger
turned on, highlighted the line ".Add Type = .... " and the error is
"Application-defined or object defined error". I am baffled because

1. The range in question is _unlocked_ one.
2. I have set the UserInterfaceOnly argument in the Protect method to
true.

Anybody has ideas on what's happened? Thanks for your advice in advance.

Frederick Chow
However,