Setting validation via VBA in excel fails when using variable

Question

I am trying to set the data validation for a range of cells using VBA. I get a run-time error 1004 (so helpful) "Application defined or object defined 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, choice is a variable passed to the function that resembles "=SomeNamedRange" from the workbook the code is in.

The error occurs on the .Add section of the code.

If I hard-code Formula1 as Formula1:="=SomeNamedRange" it works without a problem.I'd really rather not hard-code it, because I am doing this with a lot of possible values for 'choice' and that would just be less-than-clean code, I think.

I have been burning up Google and about 3 different books for days now trying to sort this out.

Any suggestions? Thanks for helping a newbie out.

Answer 1

Are you sure your choice variable's value is what you think it is? Maybe you should set a breakpoint before the .Add line and see what you're passing in. I tested the code in Excel 2003 and 2007, and it works without any issues. Only when I give the Formula1 and invalid range reference do I get the error 1004.

Can you try to run this is a new untouched workbook and see if it works for you (sure did 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 actually be a comment, not an answer, but I needed to post code so it's easier this way. I'll edit this to be my answer if I come up with one.)