Object Defined error when adding data validation

Question

I am trying to create data validation in a cell based on a string as the address. This string is saved from a worksheet.

In this example the strAddress returns $G$3:$G$14.

I get

Object-defined error

when adding the validation as indicated by * in the code.

This is half the routine, as the rest are repetitions of this:

Sub UpdateLists(advertiser As String)

'    On Error Resume Next

    'need to update the other data validation based on the advertiser selected
    Dim strAdvertiser As String, strAddress As String
    Dim adRng As Range
    Dim myRng As Range

    'clear validation first
    Range("I10:I12").Validation.Delete

    strAdvertiser = advertiser

    'now find the relevant supplier in the lists sheet
    Set adRng = Sheets("Lists").Range("A:A").Find(What:=strAdvertiser)

    If Not adRng Is Nothing Then

        'adjust division according to advertiser
        Set myRng = ActiveSheet.Cells.Find(What:="Division")

        If Not myRng Is Nothing Then
            'list range
            strAddress = adRng.Offset(0, 1).Text

            'adjust list
            '**error on below line
            myRng.Offset(0, 1).Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='Lists'!" & strAddress

        Else
            MsgBox "Failed to find Division", vbCritical
            Exit Sub
        End If
    End if
End Sub

I have tried:

a) removing the myRng object and using Sheets, but this still returns the error.

b) hard-coding the strAddress.

c) running several Debug.Print on the myRng object to check it's validity.

d) to use .Validation.Modify originally, but then have moved to .Validation.Delete and .Validation.Add.

@BigBen it is defined in the above post. See: strAddress returns $G$3:$G$14 — DDV
– DDV, Commented Jun 11, 2019 at 14:07
What version of Excel are you using? Seems to me that in older versions, you cannot use validation from a range on another worksheet. — David Zemens
– David Zemens, Commented Jun 11, 2019 at 14:14
@Dean Can you try removing ActiveSheet and fully qualifying that line? Just in case :). Also fully qualify Range("I10:I12").Validation.Delete. — dwirony
– dwirony, Commented Jun 11, 2019 at 14:20
@Dean Either one of those could've been the issue - maybe you were deleting validation on the wrong sheet (so it'd be similar to the problem FunThomas brought up), or you were establishing myRng incorrectly by finding Division on the wrong worksheet. Either way, fully qualifying all your ranges will probably save you many headaches in the future! :) — dwirony
– dwirony, Commented Jun 11, 2019 at 14:30
@Dean I'm going to guess you were deleting validation on the wrong worksheet, so FunThomas was pretty much spot on with his solution - might as well give him the credit :) — dwirony
– dwirony, Commented Jun 11, 2019 at 14:32

FunThomas · Accepted Answer · 2019-06-11 14:12:48Z

2

I think you must clear any existing validation before you set the validation to a list.

Try to change the code to

With myRng.Offset(0, 1)
    .Validation.Delete
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='Lists'!" & strAddress
End With

answered Jun 11, 2019 at 14:12

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dwirony Over a year ago

I thought this initially as well, but it looks like he might do that here: Range("I10:I12").Validation.Delete

DDV Over a year ago

Yes @dwirony you are correct. My apologies I was not clear about that

dwirony Over a year ago

@Dean Are you certain that the offset from myRng is always within "I10:I12"? Why not just do myRng.Offset(0, 1).Validation.Delete before you add just in case.

DDV Over a year ago

@dwirony yes, Debug.Print myRng.Address returns H10:H12. I added the Validation.Delete in my debugging process.

Collectives™ on Stack Overflow

Object Defined error when adding data validation

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related