Error 1004 when using VBA to set Validation from list on another s

M

MikeZz

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top