Disable Validation Input Message for whole document

[Killerkris]

Hi

I'm wondering if anyone knows how to disable the data validation input message without losing it and without doing it cell by cell.

I am using the input message as a type of help, but am wanting to turn it off if required. The best solution I could hack together was this (VBA code of course):

Cells.Select
With Selection.Validation
.Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .ShowInput = False
    End With

Which works, but loses the help, which is not what I want, and selecting 100s of different cells to find those needing it turning off isn't an option realistically

Thanks to anyone who can help[/code]

 

[Andrew Poulsom]

In the Object browser search fo Validation (a property of the Range object). Select it and press F1. In Help I get:

Validation Property

Returns the Validation object that represents data validation for the specified range. Read-only.

Click on Validation - I get a page on the Validation Object. One of the properties is Type and this is an extarct of the Help page for that:

Type Property

Returns or sets the object type, as shown in the following table.

Validation Data validation type. Can be one of the following XlDVType constants: xlValidateCustom, xlValidateDate, xlValidateDecimal, xlValidateInputOnly, xlValidateList, xlValidateTextLength, xlValidateTime, or xlValidateWholeNumber. Read-only Long.

I am surprised that Excel 2002 doesn't return the same error as Excel 2000. That's rather annoying What does this produce?

Sub Test()
    With ActiveCell.Validation
        MsgBox .Operator
    End With
End Sub

 

[Killerkris]

It did make a response, sorry if you didn't see my edit, this is all working happily now.

The problem was that before it detected the whole workbook as having the onvalidation text for some reason, when i moved all the code to a new workbook the errors and so forth appeared, a good job as this code will probably have to run on excel2000

thankyou very much for your time and code, problem solved

 

[DataAnalyst202]

Hello Andrew,

Thanks for your help on this site - I found the code above very useful. I was wondering if you could help me to alter it slightly, so that it checks every worksheet in the file.

Also, I was wondering if you could help me create a code to UNDO the code in your post above. I thought this would work but the .ShowInput line is bugging (see bold). Any help you could give woul be much appreciated - thanks so much!

Sub ApplyInputMessages()


Dim c As Range


For Each c In ActiveSheet.UsedRange
If NotHasValidation(c) Then
c.Validation.ShowInput
End If
Next c

End Sub


Function NotHasValidation(Cell As Range) As Boolean
Dim x
On Error Resume Next
x = Cell.Validation.Type
If Err = 0 Then
HasValidation = False
Else
HasValidation = True
End If
End Function