Data Validation

[Fred Lambelet]

What is the best way to validate data input? I have tried
Data>Validation, but can't figure out a way to reject
formulas. I have tried using routine in Worksheet Change
event but have to use application.undo to keep invalid
entry out of Undo list which doesn't allow user to edit
erroneous input. Any better ideas? I am fairly new to
Excel development. Any help would be greatly appreciated.

Thanks,
Fred L
(e-mail address removed)

 

[Dan E]

Fred,

Excel doesn't have a built in worksheet function to determine
if a cell contains a formula. Therefore, to do so you will need
a user defined function.

A couple of possibilities:

Public Function ISFORMULA(CellReff As Range) As Boolean
ISFORMULA = (Left(CellReff.Formula, 1) = "=")
End Function

Function HasFormula(cell)
HasFormula = cell.HasFormula
End Function

Now you'll need to populate another cell on the page with this
formula (you cannot use your UDF in the data validation dialog.
Then for your data validation use Custom -> =B5 = False
(B5 being the cell containing your UDF)

*Macro Use
Press Alt + F11
In the project explorer right click on VBAProject(Your_bookname)
and choose Insert -> module. In the code window paste the code
posted above.

More info on macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Dan E