Excel VBA List Validation - Peculiar Runtime Error

Question

I am simply trying to add a list validation to a cell and I am getting Runtime Error 1004 Application-defined or Object-defined Error. I have this exact code validating a cell elsewhere in my workbook and it has never had an issue.

With Range("Q3").Validation
        .Delete
        .Add _
        Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=Constants!Q6:Q30"
End With

I have also tried using the string "Alice,Bob" for Formula1 and I get the same error so this is not the issue. In fact, I am only seeing this error when trying to set any sort of validation (same error with xlValidateWholeNumber for instance) on this particular worksheet.

Some info that may or may not be relevant:

The worksheet is unprotected and screenupdating is off when I try to set this validation
The above is contained on a CommandButton_Click event on said worksheet
All CommandButtons in the workbook have TakeFocusOnClick = False
This is Microsoft Office 2013

Are you sure that you have a worksheet named "Constants"? Make sure you don't have a leading or trailing space in that sheet name. That error is usually thrown when it can't find that worksheet. — sous2817
Positive, like I said the exact code including the "=Constants!Q6:Q30" is used without error elsewhere in the workbook for a different cell's validation. Furthermore, it seems to be the .Add line for any sort of validation on this sheet that is causing this error (eg I got it with xlWholeNumber as well). — user3509102
Pls post a (santised) verion of your file for us to look at. — brettdj
The only other thing I can think of is your range is not be fully qualified. If you can post the whole code, it may be helpful...for instance, if you've selected a sheet above this code and then tried to run what you posted, I'm not sure if the partially qualified range reference {with Range("Q3")} would throw it for a loop. — sous2817
Thanks for your suggestions folks, I played around (mostly aimlessly :p) with the ordering of some things in the function and am no longer receiving this error. I will post some details later as to the changes I made when I'm more confident this is a permanent solution to the issue so others can compare. — user3509102

variant variant · Accepted Answer · 2014-10-01T17:11:09

I realize this is an old question, but it's still sitting as outstanding.

In my experience, you cannot set data validation to a range stored in another sheet. With one exception: if you "name" that range.

My workaround, as such, is to select the range that I need to use as a reference, give it a defined name (in Excel 2010 at least: Formulas tab > Name Manager > Define Name > follow prompts [or just type a name in the box next to the formula bar where you see B2 in my image below]), then use that named range in my code.

Adding a named range

In your case, assuming your named range is "NamedRange01", your code would be:

With Range("Q3").Validation .Delete .Add _ Type:=xlValidateList, _ AlertStyle:=xlValidAlertStop, _ Formula1:="=NamedRange01" End With

Excel VBA List Validation - Peculiar Runtime Error

1 Answers