I'm getting an automation error "The object invoked has disconnected from its
clients" Whenever I try to run a piece of code that is supposed to change the
validation on a range of cells;

Here is the code:
Sub ValidatePCPW()
Prot.UnlockDDSheet
With Sheets("New Deal Data").Range("B158:N173").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$CD$158:$CD$172"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Selection"
.InputMessage = ""
.ErrorMessage = _
"Please make your selection from the drop down box provided."
.ShowInput = True
.ShowError = True
End With

Any help with solving this problem would be greatly appreciated.

Hi
I had the same problem some time back but can't remember exactly what I did.
You may be able to solve it by:

1) Activating the sheet beforehand
2) Making sure that the cell is not in a hidden row or column

Hope these help

"Kurt Barr" wrote:

I'm getting an automation error "The object invoked has disconnected from its
clients" Whenever I try to run a piece of code that is supposed to change the
validation on a range of cells;

Here is the code:
Sub ValidatePCPW()
Prot.UnlockDDSheet
With Sheets("New Deal Data").Range("B158:N173").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=$CD$158:$CD$172"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid Selection"
.InputMessage = ""
.ErrorMessage = _
"Please make your selection from the drop down box provided."
.ShowInput = True
.ShowError = True
End With

Any help with solving this problem would be greatly appreciated.