clear validation with macro

[Pat]

When a cell containing a validation is selected I want to run a macro to
have it changed from "list" to display "any value" I have recorded a macro
but it does not work when a cell containing a validation is selected.


Private Sub ClearVal_Click()
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop,
Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

Much appreciated if you can help.
Pat

 

[JE McGimpsey]

Doesn't

Selection.Validation.Delete

do what you want?