VBA validation setting in excel fails when using variable

Question

I am trying to set data validation for a range of cells using VBA. I get error 1004 at runtime (so useful) "A specific application or object error" with this code.

With rngRangeToCheck.Cells(lrownum, 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=choice .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With 

In Formula1, selection is a variable passed to a function that resembles "= SomeNamedRange" from the workbook in which the code is located.

Error in .Add code section.

If I hard code Formula1 as Formula1:="=SomeNamedRange" , it works without problems. I really didn’t want to hard code it, because I am doing it with a lot of possible values ​​for the “choice”, and that would be just less than pure code, I think.

I have been collecting Google and about 3 different books for several days trying to figure this out.

Any suggestions? Thanks for helping the newbie.

Answer 1

Are you sure that the value of the choice variable matches your opinion? Perhaps you should set a breakpoint in front of the .Add line and see what you go through. I tested the code in Excel 2003 and 2007 and it works without any problems. Only when I give Formula 1 and the wrong link to the range, I get the error 1004.

Can you try running this new untouched book and see if it works for you (probably for me):

 Sub Test() 'Setup ' ActiveSheet.Range("B1:B2").Name = "SomeNamedRange" ActiveSheet.Range("B1").Value = "Apples" ActiveSheet.Range("B2").Value = "Oranges" Dim lrownum As Long lrownum = 1 Dim choice choice = "=SomeNamedRange" Dim rngRangeToCheck As Excel.Range Set rngRangeToCheck = ActiveSheet.Range("A1:A10") With rngRangeToCheck.Cells(lrownum, 1).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=choice .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End Sub 

(This should be a comment, not an answer, but I needed to post the code so that it was easier. I will edit this to be my answer if I come up with one.)

Answer 2

Perhaps this is also a comment, especially since this post is so old ... I had the same problem where it worked for a while, and not others. Using a dynamically named range. The solution I discovered was to temporarily block the sheet.

 Private Sub FixDropdownLists() Sheet1.Unprotect Password:="afei31afa" Dim cellv As Range For Each cellv In Sheet1.Range("B18:E18,B32:E32") With cellv.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Office_Locations" .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "Invalid Input" .InputMessage = "" .ErrorMessage = "Select the location only from the dropdown list." .ShowInput = False .ShowError = True End With Next cellv Sheet1.Protect Password:="afei31afa" End Sub 

Answer 3

This is a pretty old post, but for those who get here with the same problem as me, here is how the problem was solved in my case:

I just replaced

 With Range(Cell1,Cell2).Validation 

part

 Range(Cell1,Cell2).Select With Selection.Validation 

And tadaaaa! it works:)

Answer 4

The problem is almost certainly caused by the fact that the named range context is different from the current context in Excel.

When Validation.Add is called, Excel calculates the formula (which returns the Range object) in the current application context, and not in the workbook or sheet. So, if the named range is sheet-based and this sheet is not currently active, it will not work. If a named range exists in one workbook, but another workbook is active, this will not work. This is why your way of choosing a random cell solves the problem, as well as a .Select execution .Select , followed by Selection.Validation .

Answer 5

Make sure you don’t go to the Validation.Add formula that is in the row / column. For example, "= R2C2: R3C3" caused me problems, as soon as I changed it to "B2: C3", it worked. In addition, cells in the range MUST have values ​​in them, or you will receive an application error.