Can you toggle on and off, data validation, input messages?

[dpaton05]

I have a spreadsheet with some data validation input messages to guide the users as to instructions for using it. After they have used the spreadsheet for some time, they will become familiar in the use of it. Can you toggle all the input messages to be either on or off with the press of a button?

 

[dpaton05]

I have found some code but this code only works for the active sheet. I have multiple sheets with data validation messages and want to toggle them on and off with one button.

The code I have that works for the sheet with a code name of Start is:

Private Sub cmdTips_Click()

    Dim st As Range, qu As Range, co As Range
    
        For Each st In Start.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st
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

I thought I could add some extra code to the first procedure to toggle the tips on each sheet but it wouldn't work.

Private Sub cmdTips_Click()

    Dim st As Range, qu As Range, co As Range
    
        For Each st In Start.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st

        For Each qu In Quoting.UsedRange
            If HasValidation(qu) Then
                qu.Validation.ShowInput = Not qu.Validation.ShowInput
            End If
        Next qu
        
        For Each co In Costing.UsedRange
            If HasValidation(co) Then
                co.Validation.ShowInput = Not co.Validation.ShowInput
            End If
        Next co

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

If I try to run this procedure with the code added to disable the tips on the extra sheets, I get the error Application defined or object defined error. If I press debug, the following line of code is highlighted.

qu.Validation.ShowInput = Not qu.Validation.ShowInput

Can someone help me with possibly a better way to disable the tips on all the sheets please?

 

[Michael M]

UNTESTED

Private Sub MM1()
    Dim st As Range, qu As Range, co As Range, ws As Worksheet
  For Each ws In Worksheets
        For Each st In ws.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st
Next ws
End Sub

 

[dpaton05]

Thanks for the reply Michael,

With that code, I get the same error, just with the following line of code highlighted.

st.Validation.ShowInput = Not st.Validation.ShowInput

 

[dpaton05]

I had an idea, what about a message box that appears the first time you open a sheet, asking you if you want tips displayed. I think I like this idea. I know how to get code to run when a sheet is activated but how would i get it to run only on the first time it is activated?

 

[Michael M]

Put a counter into the workbook open event

 

[dpaton05]

Sorry Michael, all I meant was I know how to use this code to get it to run on a worksheet being opened

Private Sub Worksheet_Activate()
   
        'procedure
    
End Sub

But I do not know how add a counter that would do the described action.

 

[Michael M]

Use say range ("A1") on th first sheet
When the workbook opens the first time it will put a 1 in A1
The next time the workbook opens, if A1 contains a 1 then call the disable macro

 

[dpaton05]

I got distracted from the original question.

I have this code that runs fine for toggling on/off messages on one sheet. The button that calls this code is located on the sheet with the code name Start.

Sub ToggleTips()
    Dim st As Range, qu As Range
  
        For Each st In Start.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st
      
    '    For Each qu In Quoting.UsedRange
    '        If HasValidation(qu) Then
    '            qu.Validation.ShowInput = Not qu.Validation.ShowInput
    '        End If
    '    Next qu
End Sub

The only problem is that if I try to add code to it that does the same for a separate sheet by removing the comments in the above code, I get the error of Application defined or object defined error with the following line highlighted. The second sheet has a code name of Quoting.

qu.Validation.ShowInput = Not qu.Validation.ShowInput

 

[dpaton05]

.