Hi,
I get Error 1004 when I use the following code to set Validation using a
list on another sheet.

Lookup List Name: ProductTypes
Range to apply Validation to: rngValidate2

The list is on another page but the help seems to indicate all you have to
do is put the range name in the formula without the sheet name.

Thanks,
MikeZz

Sub Test_Add_Validation()
Dim rngValidate2 As Range

Set rngValidate2 = Selection
Call A91_Set_Basic_Validation(rngValidate2, "=ProductTypes")

End Sub

Private Sub A91_Set_Basic_Validation(rng As Range, lookupFormula)

With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=lookupFormula
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub

Mike,

It should work, check for a typo in the Lookup List Name.

Regards,
Per

"MikeZz" skrev i meddelelsen
...
Hi,
I get Error 1004 when I use the following code to set Validation using a
list on another sheet.

Lookup List Name: ProductTypes
Range to apply Validation to: rngValidate2

The list is on another page but the help seems to indicate all you have to
do is put the range name in the formula without the sheet name.

Thanks,
MikeZz

Sub Test_Add_Validation()
Dim rngValidate2 As Range

Set rngValidate2 = Selection
Call A91_Set_Basic_Validation(rngValidate2, "=ProductTypes")

End Sub

Private Sub A91_Set_Basic_Validation(rng As Range, lookupFormula)

With rng.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:=lookupFormula
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

End Sub