Stop User from Entering Formulas

[nals14]

Hi,
Is there any way for me to prevent user from entering formulas? No I do not want to change the cells to Type 'Text' because when I do that the contents of cell become ##### after 125-200 characters I think. Can I set the some protection or permission for the sheet or workbook accordingly? Is this even possible?

Thanks and Regards,
Nalini

[Barryj]

You can protect the cell, go to data validation, custom, then in the formula box type in ="" and then ok and the user will not be able to alter the contents of the cell.

[nals14]

Hi Thanks for your reply,
But I am not trying to prevent user from entering any data at all. I just don't want them to enter formulas - ie.e. even if they do start a cell with '=' Excel should ignore it.

You can protect the cell, go to data validation, custom, then in the formula box type in ="" and then ok and the user will not be able to alter the contents of the cell.

[mdmackillop]

This is a worksheet event module to remove the =
[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
Target = Application.Substitute(Target.Formula, "=", "", 1)
End Sub[/VBA]

[david000]

mdmackillop - it bugged out though if I type an = sign alone and hit enter in xl 2007
I thought about using the HasFormula, but it isn't as elegant and dosen't work with (=1+1)

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
'<//Warning Disables Undo!\\>
If Cells(1, 1) = "." Then Exit Sub 'If you need to turn this off!
On Error Resume Next
If Target.HasFormula Then
Target.Value = Target.Value
'Target = Replace(Target.Value, "=", "", 1) 'This is a do nothing line, oops!
MsgBox "No Formulas Please!"
End If
End Sub
[/vba]
I think it's always a good idea to toggle event code - sometimes having undo is a GOD send.