Turn off all Data Validation Input Messages
Posted by Joe Was on May 01, 2001 1:15 PM
We have input messages in all cells as a passive help system for inexperienced users, but would like to turn off this feature for experienced users. Microsoft said it can not be done short of turning off each cell manually!
The vb DisplayAlerts=False is not a part of input message but it would be nice if something like it exists. Any ideas?
Posted by Dave Hawley on May 01, 2001 1:37 PM
Hi Joe
I just tried to using:
Sheet3.Cells.SpecialCells(xlCellTypeAllValidation).Validation.InputMessage = ""
And it bombed out !
This one however does work.
Sub TryThis()
Dim rCell As Range
For Each rCell In Sheet3.Cells.SpecialCells(xlCellTypeAllValidation)
rCell.Validation.InputMessage = ""
Next rCell
End Sub
But the BIG problem here will be when you want to put the messages back in. The only way that springs to mind is to make a Copy of the Sheet(s) and make them xlVeryHidden the use a macro to copy and PasteSpecial Cells.SpecialCells(xlCellTypeAllValidation) into Cells.SpecialCells(xlCellTypeAllValidation) of the original. Should work Ok!
Dave
OzGrid Business Applications
Posted by Joe Was on May 01, 2001 2:12 PM
Very large application
Dave,
The application is large and setting input message to blank after saving data validation and then putting the copy back in service as a restore option if needed will work, but it will make the application too large. (We use a copy of the application for every what-if on a job, and we have a lot of what-if's.) Thinking about it though, restoring from a default workbook if needed will work with your idea, but it would be messy. PS. Microsoft has added a global turn off Input Massages' to the wish list for the next release of Excel. If implemented it should make the Input Message function more useful. Thanks for you input.
