This seems to be one of the weirdest, worst methods in the whole darn'
Excel VBA town, judging by the number of posts about it.

I THINK I've worked out why I keep getting Error 1004: Application
defined or object error. Here's the code:

Set objDBCell = ActiveWorkbook.Names("Setting_DB").RefersToRange
With objDBCell.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=strListRange
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Invalid entry"
.InputMessage = ""
.ErrorMessage = "A value from the list must be selected"
.ShowInput = False
.ShowError = True
End With

strListRange, in my tests, is

=$A$20:$A$30

Now this works IF I run the procedure (which is in a general module,
following a suggestion in another topic on this subject) from the
Immediate window.

But if it runs from the Click event of a Control Toolbox command
button, it fails on the .Add line, with that inscrutable "1004" error.

Now I just remembered there's another command button, off the Forms
toolbar. With that command button, it works fine!

So, problem solved, for me. But it seems the validation.Add method is
hypersensitive to what actually calls the code that includes it.

Oh and BTW, who had the bright idea of making data validation lists
have to be on the same worksheet as the validated cell, in XL2003,
when you could have them on any worksheet in XL2000?

If you give your list a name (e.g., ID_Num), then you can place it on a
different worksheet. In the validated cell, set Source: =ID_Num.
Bob


"sebt" wrote:

Oh and BTW, who had the bright idea of making data validation lists
have to be on the same worksheet as the validated cell, in XL2003,
when you could have them on any worksheet in XL2000?