CODEWITHSUNDEEP
vbaexcel
Gradatc
Gradatc

Reputation: 125

Setting validation via VBA in excel fails when using variable

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.

Upvotes: 4

Views: 22293

Answers (6)

Frantisek
Frantisek

Reputation: 1

I have referenced named location using the INDIRECT function and everything run perfect. Users however reported some regular, seemingly random errors and from log file I saw that this Validation.add is causing the problem. The thing is, that the Excel tool is running in background while different Excel file operated by user may have focus when this operation is executed.

Didn't test the following Code. Hope it helps.

.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:="=INDIRECT(""[" & ThisWorkbook.Name & "]" & sht_sfDocumentation.Name & "!" & gstrRANGE_NamedTableRange & """)"

Upvotes: 0

Nic Paul
Nic Paul

Reputation: 31

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

When Validation.Add is called, Excel evaluates the formula (which is returning a Range object) in the current context of the application, not the workbook or worksheet. So, if the named range is sheet-based and that sheet is not currently active, it won't work. If the named range exists in one workbook, but a different workbook is active, then it won't work. This is why your workaround of selecting a random cell solves the problem, and also the solution of performing a .Select followed by Selection.Validation.

Upvotes: 2

Tony
Tony

Reputation: 1

Make sure that you are not passing into the Validation.Add a formula that is in row/column. For example "=R2C2:R3C3" caused me problems, as soon as I changed it to "B2:C3" it worked. Also the cells in the range MUST have values in them, or you will get application error.

Upvotes: 0

Torhi
Torhi

Reputation: 31

This is a pretty old post but for anyone getting here with the the same issue I did, here is how the problem was solved in my case:

I just replaced the 

With Range(Cell1,Cell2).Validation

part by

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

And tadaaa! it works :)

Upvotes: 3

Gruzzles
Gruzzles

Reputation: 291

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

Upvotes: 5

jevakallio
jevakallio

Reputation: 35890

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

Upvotes: 3

Related Questions