Protected sheet: Automation error for validation
 

Hi,

I have a protected worksheet with column J locked. This worksheet has
worksheet_change event coding for calculating values in column j
depending on values in previous columns. I am using Worksheet.protect
method in the worksheet_change code to input values in the locked
column J.

Column A (unlocked cells) of the protected worksheet has a validation
object which gives a drop down list in column A.It works properly till
another sheet in the workbook is activated. When the protected sheet
is activated again after activating another sheet, the validation
object in Column A gives an "Automation error". If I unprotect the
sheet through the user interface then the validation starts working
again.

How do I overcome this problem?

Thanks in advance for the help.

Regards,
Raj