On Feb 8, 4:15 pm, "Peter T" <peter_t@discussions wrote:
You need to ensure a cell range is selected when you run that code.

If you run the code from a worksheet control set its TakeFocusOnClick
property to false.

However, as the code would also fail if any other type of object is
selected, perhaps select some cell as the first line of your code, eg

ActiveWindow.VisibleRange(1, 1).Select

Regards,
Peter T

wrote in message

oups.com...

Hello,

I have a Data Validation in a cell say D5 of type xlValidateList.

I modify this validation in VBA:

sub foo ()
Range("d5").Validation _
.Modify Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Formula1:="Apple,Orange,Mango"
end sub

This works when run from the VB Editor.

If I create a Control Toolbox button and put the same code in say :
CommandButton1_Click it does not work.
Says "Application-Defined or Object-Defined error"

Again if I create a Forms Button and put the same code in the macro
assigned to the button it works.

Why doesn't it work when triggered from the Control Toolbox Button?

Regards
Gap

Thanks Peter, your solution of "Select"ing the range works. (Since
there are other object on the sheet that could be selected). Sometimes
it confuses the user though.

Would you call this a workaround to the problem or thats how it should
be done?