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]

 

[miccysoft]

my suggest to you would be to write your own validation macro. then you can control what you want much easier

 

[Killerkris]

I'm actually not caring about the validation, I'm just wanting the equivilent of popup help without the office assistant being used.

Is it possible to have the input message in a custom writen validation function, if so it sounds like a good plan, do you know the area in VBA where you would write such a macro?

Thanks

 

[miccysoft]

Im sure its possible. I did something similiar with VB but im not sure about excel im afraid. perhaps a macro in a module could be triggered from a validation call in the sheet maybe?

 

[Norie]

Kris

Why not use Comments?

 

[jimbojones]

Hi,

I think you need to loop through the cells and allow for those without validation with a resume next on error statement.

Something like this will toggle between the two states of shown or not. Will take a while for all cells in sheet though!

Option Explicit
Dim Cell As Range

Sub A()

For Each Cell In Selection
On Error Resume Next
Cell.Validation.ShowInput = Not Cell.Validation.ShowInput
Next Cell

End Sub


James

 

[Andrew Poulsom]

This will toggle the display of help on and off, without losing the message.

Sub Test()
    Dim c As Range
    For Each c In ActiveSheet.UsedRange
        If HasValidation(c) Then
            c.Validation.ShowInput = Not c.Validation.ShowInput
        End If
    Next c
End Sub

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

 

[Killerkris]

Thanks a lot for the code andrew, unfortunately the Validation.Type (at least in my version of Excel) always returned 0, so I just changed it around to be on the validation message:

If Not c.Validation.InputMessage = "" Then
MsgBox "There is validation"
Else
MsgBox "No validation"
End If

Thanks to everyone for their help, I think I'm all solved here

And PS on the comments: Comments are going to be used for other purposes hence why I didn't use them

 

[Andrew Poulsom]

What version of Excel are you using?

In Excel 2000 the Types are:

xlValidateCustom - 7
xlValidateDate - 4
xlValidateDecimal - 2
xlValidateInputOnly - 0
xlValidateList - 3
xlValidateTextLength - 6
xlValidateTime - 5
xlValidateWholeNumber - 1

If you try to access the Type property for a cell that doesn't have Validation Excel generates error 1004. My HasValidation function takes advantage of this.

What happens if you run this when the ActiveCell doesn't have Validation?

Sub T()
    MsgBox ActiveCell.Validation.Type
End Sub

 

[Killerkris]

Sorry for the slow response, in actual fact I haven't been able to get it working and was messing around trying to work out why.

The .Type always returns 0 in the messagebox, no matter whether there is validation or not

As for my Excel, I'm running 2002, and In the standard VBA help it doesnt specify the type errors

thanks for your time

Edit: Ok, it was my workbook, for some reason all of the cells were seen as having input messages, even tho the boxes etc were unset. (I guess my playing around must have done that). So, it works great, thanks a lot for that, much appreciated