Toggle Input Messages On and Off (Validated Cells)

[jw191]

Hi,

I have a large Excel workbook, in which I have used validation on many cells accross multiple worksheets. I have put input messages in each of the cells with validation but want to provide the user with the capability to turn these on and off.

I tried the following code:

Sub HideInputMessages()
Dim thissheet As String

thissheet = ActiveSheet.Name

For loopsheets = 1 To ActiveWorkbook.Sheets.Count
Sheets(loopsheets).Select
Cells.Select
    With Selection.Validation
        .ShowInput = False
    End With
Next
Sheets(thissheet).Select

End Sub

Unfortunately this code returns errors, probably becasue not all cells have vaidation and different cells have different validation.

Can anyone suggest a script that might work?

Thanks,

JW191.

[Andy Pope]

this will toggle

Sub HideInputMessages()

    Dim shtTemp As Worksheet
    Dim rngCells As Range

    On Error Resume Next
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        Set rngCells = shtTemp.Cells.SpecialCells(xlCellTypeAllValidation)
        If Not rngCells Is Nothing Then
            rngCells.Validation.ShowInput = Not rngCells.Validation.ShowInput
        End If
        Set rngCells = Nothing
    Next

End Sub

[jw191]

this will toggle

Sub HideInputMessages()

    Dim shtTemp As Worksheet
    Dim rngCells As Range

    On Error Resume Next
    
    For Each shtTemp In ActiveWorkbook.Worksheets
        Set rngCells = shtTemp.Cells.SpecialCells(xlCellTypeAllValidation)
        If Not rngCells Is Nothing Then
            rngCells.Validation.ShowInput = Not rngCells.Validation.ShowInput
        End If
        Set rngCells = Nothing
    Next

End Sub

Hi Andy,

Many Thanks for your reply - unfortunately I can't get teh code to work. I removed the On Error statement and it seems to stall on the line:

Set rngCells = shtTemp.Cells.SpecialCells(xlCellTypeAllValidation)

Also was this code intended to switch on or off the input messages? Or does it 'toggle' them - i.e. the opposite of the state they are in now?

Many thanks,

James.

[Andy Pope]

The code toggles.

Removing the error trapping will cause the code to raise a debug warning.

Can you post your non working workbook.