VBA Inserted Data Validation Fails

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

_RickK - 23 Jan 2007 23:01 GMT

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.

Reply to this Message

Doug Glancy - 23 Jan 2007 23:13 GMT

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

>I am trying to use Dynamic Data Validation inserted at run time but it
>fails
[quoted text clipped - 18 lines]
>
> Any help on this will be greatly appreciated.

Reply to this Message