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

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

I couldn't reproduce the problem. did you add the code into the sheet code
module?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



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

Hi Bob,

I could replicate the problem in XL2000 & XL97

As I mentioned, if the selection while running the code is not a Range, eg a
Controls button with focus or a Rectangle, the code fails for me. Not only
with the OP's particular code but whenever adding or modifying the DV.

Regards,
Peter T


"Bob Phillips" wrote in message
...
I couldn't reproduce the problem. did you add the code into the sheet
code
module?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)



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

Yes the code is in the Sheet.

On Feb 8, 5:59 pm, "Bob Phillips" wrote:
I couldn't reproduce the problem. did you add the code into the sheet code
module?

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

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

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?

Try using

Activecell.Select

instead, that shouldn't confuse.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



wrote in message
ps.com...
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?