How do I set data validation in VBA

[Guest]

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.

 

[Guest]

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

 

[Guest]

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 Tools>Add-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

 

[Guest]

Data validation is an inherent functionality of Excel itself - it does not
depend on any addins - nor does VBA in general. Analysis Toolpak = VBA is
only required to use the functions provided in the Analysis toolpak addin in
VBA- other than that, it has no affect on VBA functionality.

by the way, I tested your code and my revision of it on xl2003.