VBA .Validation.Add run-time error 1004

[JackDanIce]

Hi,

I have the following code which is giving a run-time error message, though was working fine earlier.

Formula1 (List_Status)is a named range which hasn't changed location,address or values. The range value when error occurs is found in List_Status

Line of error (red), rng.address is A19 and rng.parent.name is name of the activesheet this occurs on

Public Sub Add_Validation(ByRef rng as Range)

With Application
  .ScreenUpdating = False
  .EnableEvents = False
End With

With rng        
  If Len(.Value) > 0 Then
    .Validation.Delete
    .Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=List_Status"
  End If
End With

With Application
  .ScreenUpdating = True
  .EnableEvents = True
End With

End Sub

Searching online seems to generally suggest using .Validation.Delete prior to validation.add but I already have this line so unclear as cause of error. Excel 2013

Any ideas?

Thanks in advance,
Jack

 

[JackDanIce]

Sheet is unprotected too

 

[RoryA]

Are you certain that List_Status is a valid range at the time?

 

[JackDanIce]

Yes, in the debug window I received a result of 3 to:

? Application.Match(rng.Value, Range("List_Status"), 0)
3

 

[RoryA]

What's the scope of List_Status? Workbook, or worksheet?

 

[JackDanIce]

Workbook, though I tried changing the formula to "=Admin!List_Status" (It's on sheet "Admin") and still same error.

 

[RoryA]

Do you have more than one sheet selected?

 

[JackDanIce]

No, I'm checking through previous subs/functions and events for sheet protection, only other thing I can think of. And before clicking reply - I just checked, the sheet property is showing it's protected, I'm sure it wasn't when code first errored today.

Changed code to force unlock before the .Validation.Delete line and it's fixed it.

Thanks for the replies and help RoryA