Thanks, Tom. I thought that is the response I might get. I recently got a
new CPU at work and I don't have as much functionality with Excel 2003 as I
had with Excel 2002. In ToolsAdd-Ins, I have the "Analysis ToolPak - VBA"
included. Do you know if I would need a different add-in? Any other
suggestions from anyone?

Thanks again, Brad

"Tom Ogilvy" wrote:

that worked for me. In fact this worked as well:

Sub aatester1()
Range("A5:A13").Validation.Delete
Range("A5:A13").Validation.Add _
Type:=xlValidateCustom, _
Formula1:="=COUNTIF(INDIRECT($K$27),A5)=1"
End Sub

--
Regards,
Tom Ogilvy


"Brad E." wrote:

In the Worksheet_Change event, I want to change data validation depending on
certain entries in the spreadsheet. When the VBA gets to the following code:

Range("A5").Validation.Delete
Range("A5").Validation.Add Type:=xlValidateCustom,
Formula1:="=COUNTIF(INDIRECT($K$27),A5)=1"

where K27 = "A5:A13", and I don't want a duplicate in A5:A13.

I get the following error message: "Method 'Add' of object 'Validation'
failed". Does anyone know why my VBA won't add data validation?

Thanks in advance for any help. Brad E.