How do I set data validation in VBA

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.

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.

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

"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.

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.

--
Regards,
Tom Ogilvy


"Brad E." wrote:

> 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
>
> "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.