Luisa,
As long as the formula returns a value that matches the criteria, data validation won't work.
You'll need to use the worksheet change event to check the entry of the cell of interest.
The example change event code below will prevent a user from entering a formula in cell B4 of the sheet whose codemodule contains
the event code. Note that the code should not be entered into a regular codemodule.
HTH,
Bernie
Excel MVP
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
If Target.HasFormula Then
Application.EnableEvents = False
Target.ClearContents
MsgBox "Please do not enter a formula in cell B4."
Target.Select
Application.EnableEvents = True
End If
End If
End Sub
Hi:
Thank you for your answer.
Ok, I've done it. But if I want I continue with the
possibility to create a formula in cell in the formula bar.
I want a input value in the cell who is validate with data
validation, but I don´t want the user has the abillity to
define a formula in that cell.
-----Original Message-----
Luisa,
You need to first Lock the cells: Select all the cells,
then use Format Cells, Protection tab, check Locked.
Then select the cells
you want to be able to change, and unlock those cells.
Then when you protect the sheet, the cells that are still
locked won't be
able to be changed. Put a password if you want to be
doubly sure, but make sure you can remember it easily....
