Application-Defined or Object-Defined error

[3link]

I have a macro that deletes validation criteria within a few ranges and implements new criteria when activated. It looks like this:

ActiveSheet.Unprotect
With Range("H10").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=PTsRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H10] = Range("BM43").Value
 
  With Range("H16").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=LrRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H16] = Range("BQ44").Value
 
   With Range("H22").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=RcRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H22] = Range("BS44").Value
 
    With Range("H28").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=LgRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H28] = Range("BU44").Value
 With ActiveSheet.SpinButton6
       .Min = 0
       .Max = Range("BN44").Value
       .Value = Range("BN44").Value
       End With
With ActiveSheet.SpinButton7
       .Min = 0
       .Max = Range("BQ44").Value
       .Value = Range("BQ44").Value
       End With
With ActiveSheet.SpinButton8
       .Min = 0
       .Max = Range("BS44").Value
       .Value = Range("BS44").Value
       End With
With ActiveSheet.SpinButton9
       .Min = 0
       .Max = Range("BU44").Value
       .Value = Range("BU44").Value
       End With
       ActiveSheet.Protect

The named ranges (PTsRange, LRrange, RCrange and LGrange) are all labeled and functioning properly. I tested them on separate validation lists to confirm. However, I still get this Application-Defined or Object-Defined error run-time error. When I get it,

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

is highlighted.

Thus, the error must be in the way the macro is coded. I can't see a darn thing wrong with it. Anyone have any ideas?

 

[perco754]

Wow, I get exactly the same error from the exact cause ... strange nobody has replied. Is it a very complex issue this one?

rng is a valid range (I've verified its address using the immediate window)

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

I have a macro that deletes validation criteria within a few ranges and implements new criteria when activated. It looks like this:

ActiveSheet.Unprotect
With Range("H10").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=PTsRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H10] = Range("BM43").Value
 
  With Range("H16").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=LrRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H16] = Range("BQ44").Value
 
   With Range("H22").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=RcRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H22] = Range("BS44").Value
 
    With Range("H28").Validation
 .Delete
 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
 Operator:=xlBetween, Formula1:="=LgRange"
 .ErrorMessage = "Invalid value. Select one from the dropdown list."
 .InCellDropdown = True
 End With
 Sheets("Trends").[H28] = Range("BU44").Value
 With ActiveSheet.SpinButton6
       .Min = 0
       .Max = Range("BN44").Value
       .Value = Range("BN44").Value
       End With
With ActiveSheet.SpinButton7
       .Min = 0
       .Max = Range("BQ44").Value
       .Value = Range("BQ44").Value
       End With
With ActiveSheet.SpinButton8
       .Min = 0
       .Max = Range("BS44").Value
       .Value = Range("BS44").Value
       End With
With ActiveSheet.SpinButton9
       .Min = 0
       .Max = Range("BU44").Value
       .Value = Range("BU44").Value
       End With
       ActiveSheet.Protect

The named ranges (PTsRange, LRrange, RCrange and LGrange) are all labeled and functioning properly. I tested them on separate validation lists to confirm. However, I still get this Application-Defined or Object-Defined error run-time error. When I get it,

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

is highlighted.

Thus, the error must be in the way the macro is coded. I can't see a darn thing wrong with it. Anyone have any ideas?