Selecting every sheet

[dpaton05]

I have 2 worksheets that both have tool tips in the form of data validation input messages. There is a button on each sheet to toggle them on or off. Both buttons link to this procedure.

Sub ToggleTips()
Quoting.Unprotect Password:=ToUnlock
Dim st As Range, cell As Range
        For Each st In ActiveSheet.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st
Quoting.Protect Password:=ToUnlock
End Sub

What code do I put for this sub to allow me to have just one button but it to toggle the tips on every sheet?

 

[Michael M]

Try using

Sub ToggleTips()
Dim st As Range, cell As Range, ws As Worksheet
    For Each ws In Worksheets
    If ws.Name = "SheetNAME" Or ws.Name = "SheetName" Then
        ws.Activate
        ws.Unprotect Password:=ToUnlock
        For Each st In ActiveSheet.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st
        ws.Protect Password:=ToUnlock
    Next ws
End Sub

 

[dpaton05]

I get an error, Next without for and the next on 2nd last line is highlighted.

 

[dpaton05]

Thanks for helping today Michael. I need to go now but I will look at this some more when I am next at work on Wednesday.

 

[Michael M]

I thought you could have debugged and If ...End If error

Sub ToggleTips()
Dim st As Range, cell As Range, ws As Worksheet
    For Each ws In Worksheets
    If ws.Name = "SheetNAME" Or ws.Name = "SheetName" Then
        ws.Activate
        ws.Unprotect Password:=ToUnlock
        For Each st In ActiveSheet.UsedRange
            If HasValidation(st) Then
                st.Validation.ShowInput = Not st.Validation.ShowInput
            End If
        Next st
        ws.Protect Password:=ToUnlock
    End If
    Next ws
End Sub

 

[dpaton05]

Thanks for that Michael, I was looking for the next lines to loop the for each loops andI completely forgot about ending the if statements.

 

[Michael M]

No worries...