Xl2003: Validation.Add method gives error from Command Button
 

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.