object invoked has disconnected from its clients

[kaywin]

For one column we have used addType validation. Below is the syntax we have used.
.Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="0", Formula2:="999999999999999999"

The same is working fine when we don't protect the sheet. However for project constraints we are protecting the workbook & the work sheet due to which we are getting the error as below.

object invoked has disconnected from its clients.

Please help us to inorder to overcome the above mentioned error.

Thanks & Regds
Kaywin

 

[GlennUK]

Can you show the code that does the protecting?

In what order do your tasks take place? ( I mean, do you try to add data validation after applying protection? )

 

[kaywin]

We are not protecting the sheet thro VBA code...we are protecting workbook and sheet from Tools->Protection..

First we have added the Data validation through vba code...then we protected the sheet..

After protecting the sheet,this data validation is not working.. its throwing error in .AddType

Thanks

 

[GlennUK]

How is it that your code is running after you apply protection exactly? You say "First we have added the Data validation through vba code", so why is your code trying to run again later?

 

[outhwaik]

Has anyone discovered a solution to this problem? I have pretty much the exact same issue (VBA code running fine until protecting the worksheet using Tools>Protection, error on line .AddType, 'object invoked disconnected from its clients'). Any help much appreciated =)

 

[outhwaik]

Typical, as soon as I post somewhere for help I find a solution!

To anyone who is interested, this is the code I used to get it working:

For Each sh In ActiveWorkbook.Worksheets
sh.Unprotect
Next sh

With Selection.Validation
...
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=list
....
End With

For Each sh In ActiveWorkbook.Worksheets
sh.Protect
Next sh