VBA Inserted Data Validation Fails
 

I am trying to use Dynamic Data Validation inserted at run time but it fails
consistantly with : Method 'Add' of Object 'Validation' failed.

The most basic snippet I tried was:
-------------------------------------------
---sub cmdClick()

Dim wks As Worksheet

Set wks = Worksheets("sheet1")

wks.Range("A1").Validation.Add xlValidateList, , , "=1,2,3"


The parent sheet is full of ADO 2.8 and other code that is fine. It is only
this functionality that fails.

Is there some other property to set or enable? I cannot find anywhere
speicifying any other object requirements.

Any help on this will be greatly appreciated.

VBA Inserted Data Validation Fails
 

RickK

Two things:

Get rid of the equal sign in your list.
Also, if you run this more than once, you need to delete the validation,
before you can add it:

With wks.Range("a1").Validation
.Delete
.Add xlValidateList, , , "1,2,3"
End With

hth,

Doug
"_RickK" wrote in message
...
I am trying to use Dynamic Data Validation inserted at run time but it
fails
consistantly with : Method 'Add' of Object 'Validation' failed.

The most basic snippet I tried was:
-------------------------------------------
---sub cmdClick()

Dim wks As Worksheet

Set wks = Worksheets("sheet1")

wks.Range("A1").Validation.Add xlValidateList, , , "=1,2,3"


The parent sheet is full of ADO 2.8 and other code that is fine. It is
only
this functionality that fails.

Is there some other property to set or enable? I cannot find anywhere
speicifying any other object requirements.

Any help on this will be greatly appreciated.

VBA Inserted Data Validation Fails
 

RickK,

It works for me and I can't think of what the issue might be.

Doug

"_RickK" wrote in message
...
No luck. Still have the same error popping up. I am on 2003 SP2 and also
got the same error on 2000.

"Doug Glancy" wrote:

RickK

Two things:

Get rid of the equal sign in your list.
Also, if you run this more than once, you need to delete the validation,
before you can add it:

With wks.Range("a1").Validation
.Delete
.Add xlValidateList, , , "1,2,3"
End With

hth,

Doug
"_RickK" wrote in message
...
I am trying to use Dynamic Data Validation inserted at run time but it
fails
consistantly with : Method 'Add' of Object 'Validation' failed.

The most basic snippet I tried was:
-------------------------------------------
---sub cmdClick()

Dim wks As Worksheet

Set wks = Worksheets("sheet1")

wks.Range("A1").Validation.Add xlValidateList, , , "=1,2,3"


The parent sheet is full of ADO 2.8 and other code that is fine. It is
only
this functionality that fails.

Is there some other property to set or enable? I cannot find anywhere
speicifying any other object requirements.

Any help on this will be greatly appreciated.

VBA Inserted Data Validation Fails
 

The sheet isn't protected, is it? That would get you the error.

Doug

"Doug Glancy" wrote in message
...
RickK,

It works for me and I can't think of what the issue might be.

Doug

"_RickK" wrote in message
...
No luck. Still have the same error popping up. I am on 2003 SP2 and
also
got the same error on 2000.

"Doug Glancy" wrote:

RickK

Two things:

Get rid of the equal sign in your list.
Also, if you run this more than once, you need to delete the validation,
before you can add it:

With wks.Range("a1").Validation
.Delete
.Add xlValidateList, , , "1,2,3"
End With

hth,

Doug
"_RickK" wrote in message
...
I am trying to use Dynamic Data Validation inserted at run time but it
fails
consistantly with : Method 'Add' of Object 'Validation' failed.

The most basic snippet I tried was:
-------------------------------------------
---sub cmdClick()

Dim wks As Worksheet

Set wks = Worksheets("sheet1")

wks.Range("A1").Validation.Add xlValidateList, , , "=1,2,3"


The parent sheet is full of ADO 2.8 and other code that is fine. It
is
only
this functionality that fails.

Is there some other property to set or enable? I cannot find anywhere
speicifying any other object requirements.

Any help on this will be greatly appreciated.