Error 1004 when using VBA to set Validation from list on another s
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 |
Error 1004 when using VBA to set Validation from list on another s
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 |
All times are GMT +1. The time now is 04:12 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com