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

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

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

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


--
HTH,
Bernie
MS Excel MVP


wrote in message
...
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

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

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

Michael