CODEWITHSUNDEEP
excelvba
DDV
DDV

Reputation: 2385

Object Defined error when adding data validation

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.

Upvotes: 1

Views: 155

Answers (1)

FunThomas
FunThomas

Reputation: 29566

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

Upvotes: 2

Related Questions