Possible Validation.Modify bug in Excel 2000

[gappodi]

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

 

[Peter T]

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

 

[Bob Phillips]

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)

 

[Peter T]

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

 

[gappodi]

Yes the code is in the Sheet.

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)

 

[gappodi]

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

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?

 

[Bob Phillips]

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)