Problems on Protected Sheet
 

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,

Problems on Protected Sheet
 

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,

Problems on Protected Sheet
 

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,