Clearing Validations from many worksheets

S

Simka

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
 
J

john

Try this:

Sub ClearValidation()

For Each ws In ActiveWorkbook.Worksheets

ws.Cells.Validation.Delete

Next ws

End Sub
 
S

Simka

Cheers John,

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