Run-time error '-2147417848 (80010108) with data validation

[ogo]

Hi,

I'm trying to automatically update the list of items for data validation in multiple cells. Since I wasn't sure how to access data validation, I just quickly recorded a macro, and worked off that code. When I try to run the code I came up with I get the following error:

"Run-time error '-2147417848 (80010108)':

Automation error
The object invoked has disconnected from its clients."

I have two sheets in my workbook and i am copying data from one sheet to other in some other part of the code. Can anyone help me.

Cells(57 ,4).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="true,false"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = " "
.ErrorTitle = " "
.InputMessage = " "
.ErrorMessage = " "
.ShowInput = True
.ShowError = True
End With



Thanks in advance,
Ogo

 

[GlennUK]

Have you turned calculation off? Why are you putting " " as titles and messages instead of ""? In fact try removing those rows of code.

 

[ogo]

Hi Glenn,

i tried with Application.Calculation = xlManual
But the code throws the same error. I have seen this error in many posts, but didn't find a solution.

Thanks,

Ogo

 

[RoryA]

Which version of Excel?
Does it help if you don't select the cell first:

With Cells(57 ,4).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="true,false"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = " "
.ErrorTitle = " "
.InputMessage = " "
.ErrorMessage = " "
.ShowInput = True
.ShowError = True
End With

 

[ogo]

Thanks Rory, with the the modification as you suggested, its working.
What was the actual reason behind the problem? I still didn't get it. I am currently using excel2003.

Thanks,
Ogo

 

[RoryA]

My guess was an issue with the dropdown being active when you delete and reset it. Not selecting the cell initially removed that problem (not that it should be a problem, IMO - I think it's a bug)

 

[ogo]

This was very helpful, thank you once again Rory.

Ogo