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. |
Xl2003: Validation.Add method gives error from Command Button
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? |
Xl2003: Validation.Add method gives error from Command Button
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? |
All times are GMT +1. The time now is 08:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com