Remove Validation Input Messages

[Code Numpty]

Someone from another forum kindly gave me this code to remove all validation
input messages from a worksheet but it doesn't work or throw any error
messages.
............................................................................
Sub RemoveValidationInputMsgs()
On Error GoTo None
With
ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation).Validation
.InputTitle = Empty
.InputMessage = Empty
End With
None:
On Error GoTo 0
End Sub
............................................................................
Any help appreciated with this, thanks.

 

[Mike H]

Hi,

It works for me. Note that as written it only works on the active sheet, do
you have the sheet you want it to work on selected?

Mike

 

[Peter T]

Your code wouldn't throw any error messages due to use of the error handler,
try commenting On error resume next.

If the code fails it is probably due to mixed validation types on the sheet
or protection. If due to the mixed types it means looping each validation
cell, unless you have some other way of identifying areas that contain same
type of validation..

Sub NoDVinputMsg()
Dim rng As Range, cel As Range
Set rng = Nothing ' only if rng previously set
On Error Resume Next
Set rng = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
If Not rng Is Nothing Then
bDummy = rng.Validation.ShowInput
If Err.Number = 0 Then
' all same type, no need to loop
With rng.Validation
.InputTitle = ""
.InputMessage = ""
End With
Else
On Error GoTo 0
For Each cel In rng
With cel.Validation
.InputTitle = ""
.InputMessage = ""
End With
Next
End If
End If
End Sub

BTW, if you only want to deisable the input messages and not permanently
remove them, just do
..ShowInput = False

Regards,
Peter T

 

[Code Numpty]

Thanks Peter, I commented out On Error GoTo None and On Error GoTo 0 and got
error code 1004 and the debugger highlighted .InputTitle = Empty.

So, I used your code which of course works like a dream, thank you for your
help.