Hi All,

I am trying to clear all the validations which have been set up on a large
number of worksheets and I am trying this routine but it fails and I'm
getting an 'Error code 438' and I'm not sure how to correct it...Any
Suggestions?

Thanks.

Sub ClearValidation()

For Each WS In ActiveWorkbook.Worksheets

With WS.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With

Next WS

End Sub

Try this:

Sub ClearValidation()

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Validation.Delete

Next ws

End Sub

--
jb


"Simka" wrote:

> Hi All,
>
> I am trying to clear all the validations which have been set up on a large
> number of worksheets and I am trying this routine but it fails and I'm
> getting an 'Error code 438' and I'm not sure how to correct it...Any
> Suggestions?
>
> Thanks.
>
> Sub ClearValidation()
>
> For Each WS In ActiveWorkbook.Worksheets
>
> With WS.Validation
> .Delete
> .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
> Operator _
> :=xlBetween
> .IgnoreBlank = True
> .InCellDropdown = True
> .ShowInput = True
> .ShowError = True
> End With
>
> Next WS
>
> End Sub
>

Cheers John,

That was pretty easy! Just a simple case of entering the word 'cells'. I
should've thought of that!




"john" wrote:

> Try this:
>
> Sub ClearValidation()
>
> For Each ws In ActiveWorkbook.Worksheets
>
> ws.Cells.Validation.Delete
>
> Next ws
>
> End Sub
>
> --
> jb
>
>
> "Simka" wrote:
>
> > Hi All,
> >
> > I am trying to clear all the validations which have been set up on a large
> > number of worksheets and I am trying this routine but it fails and I'm
> > getting an 'Error code 438' and I'm not sure how to correct it...Any
> > Suggestions?
> >
> > Thanks.
> >
> > Sub ClearValidation()
> >
> > For Each WS In ActiveWorkbook.Worksheets
> >
> > With WS.Validation
> > .Delete
> > .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
> > Operator _
> > :=xlBetween
> > .IgnoreBlank = True
> > .InCellDropdown = True
> > .ShowInput = True
> > .ShowError = True
> > End With
> >
> > Next WS
> >
> > End Sub
> >