PDA

View Full Version : Stop User from Entering Formulas



nals14
09-07-2007, 10:49 AM
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? :think:

Thanks and Regards,
Nalini

Barryj
09-07-2007, 12:51 PM
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
09-07-2007, 01:00 PM
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
09-07-2007, 01:08 PM
This is a worksheet event module to remove the =
Private Sub Worksheet_Change(ByVal Target As Range)
Target = Application.Substitute(Target.Formula, "=", "", 1)
End Sub

david000
09-07-2007, 04:06 PM
mdmackillop (http://vbaexpress.com/forum/member.php?u=87) - 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)


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

I think it's always a good idea to toggle event code - sometimes having undo is a GOD send.