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

This probably should be a comment too, especially since this post is so old...I had the same problem, where it would work some of the time and not others. Using a dynamically named range. The solution that I found was to unlock the sheet temporarily.

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 2

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.)