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` — Dean, Jun 11 '19 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, Jun 11 '19 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, Jun 11 '19 at 14:20
@dwirony thanks for this, it works now. Perhaps you could elaborate as to why using `ActiveSheet` would cause issues? — Dean, Jun 11 '19 at 14:28
@dwirony if you post answer with explanation I can mark as answered, thanks. — Dean, Jun 11 '19 at 14:29
@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, Jun 11 '19 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, Jun 11 '19 at 14:32
@Dean here's a primer on why (and how) to avoid using Activate/Select: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/ — David Zemens, Jun 11 '19 at 14:40

score 2 · Answer 1 · answered Jun 11 '19 at 14:12

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 '19 at 14:12

FunThomas

23,043
3
18
34

I thought this initially as well, but it looks like he might do that here: `Range("I10:I12").Validation.Delete` – dwirony Jun 11 '19 at 14:14
Yes @dwirony you are correct. My apologies I was not clear about that – Dean Jun 11 '19 at 14:15
3

@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. – dwirony Jun 11 '19 at 14:16
@dwirony yes, `Debug.Print myRng.Address` returns `H10:H12`. I added the `Validation.Delete` in my debugging process. – Dean Jun 11 '19 at 14:18

Object Defined error when adding data validation

1 Answers1