A macro that will inable/disable input message boxes by a cell'svalue

[MichaelRLanier]

I would like to be able to inable and disable the input message boxes
(Data > Validation > Input Message) in all worksheets according to the
dropdown message ("on" or "off") I select in cell A1 in Sheet1. I use
Excel 2003. Are there any suggestions? Thanks for your help.

Michael

 

[Bernie Deitrick]

Michael,

Assuming that you only have one type of datavalidation per any cell with validation....


Sub ToggleInputMessages()
Dim mySh As Worksheet
Dim myR As range
Dim myA As range
Dim TurnOn As Boolean

TurnOn = False

If Worksheets("Sheet1").Range("A1").Value = "on" Then TurnOn = True

For Each mySh In ActiveWorkbook.Worksheets
Set myR = Cells.SpecialCells(xlCellTypeAllValidation)
If myR Is Nothing Then GoTo NoValidation
For Each myA In myR.Areas
myA.Cells.Validation.ShowInput = TurnOn
Next myA
NoValidation:
Next mySh
End Sub

 

[MichaelRLanier]

Thanks Bernie. I'll be giving it a try shortly.

Michael