Application-Defined or Object-Defined error run-time error

[perco754]

I get this error while trying to run the code below. It stops right after the .Delete line i.e. on the .Add line. Funny is, after the delete the rng is still responding TRUE on rng.Validation.Value. I thought the value should be FALSE after a deletion?

(rng is a valid range validated in the immediate window where I can see its correct address etc. and the rList is also OK holding the address of the validation list, finally No the sheet is NOT protected)

With rng
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With

 

[Andrew Poulsom]

What is rList exactly? If it's a named range:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=rList"

 

[Peter_SSs]

rng is a valid range ... and the rList is .. the address of the validation list

If the above is true then I think your code should work. This works for me. Does it work for you, stand-alone?

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DV()<br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> rList <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rng = Range("A1:A5")<br>    rList = Range("I1:I3").Address<br>    <SPAN style="color:#00007F">With</SPAN> rng<br>        <SPAN style="color:#00007F">With</SPAN> .Validation<br>            .Delete<br>            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _<br>                xlBetween, Formula1:="=" & rList<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[perco754]

No rList points to an address on the very same sheet where the validation is, holding the values (strings) used by the validation
Thanks!

What is rList exactly? If it's a named range:

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=rList"

 

[perco754]

Noop sorry, but the code has been working fine for almost a year now. Suddenly it won't work anymore. I think probably the cause is some other place within the code prioe to these lines.
Right now I really haven't got a clue where to look next

Thanks replying!

If the above is true then I think your code should work. This works for me. Does it work for you, stand-alone?


Sub DV()
Dim rng As Range
Dim rList As String

Set rng = Range("A1:A5")
rList = Range("I1:I3").Address
With rng
With .Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & rList
End With
End With
End Sub

 

[Andrew Poulsom]

What is in rList when the code fails?

 

[Peter_SSs]

Noop sorry, but the code has been working fine for almost a year now.

Does the "Noop" mean that my code, on a fresh worksheet does not apply Data Validation to A1:A5 as a drop-down list of values stored in I1:I3?

 

[perco754]

No sorry, what I meant was that the (almost apart from obvious different range values etc.) code won't work within my macros. I'm sure it would work as a stand-alone sub though.

I don't know why .Delete seems to work but not the .Add

BR,
perco

Does the "Noop" mean that my code, on a fresh worksheet does not apply Data Validation to A1:A5 as a drop-down list of values stored in I1:I3?

 

[Peter_SSs]

Perhaps you had better answer Andrew's question and, if it isn't too long, could we see the rest of the code?

 

[perco754]

the correct address to a range containing the string values of the validation list.

(I tried to upload an image showing a screen dump but the URL wasn't accepted??? )

What is in rList when the code fails?