Do not allow formulas in cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We have a spreadsheet that we would like to restrict cell entries to numbers only. We have used conditional formatting and validation and the user can still enter a formula. If the formula evaluates to a valid entry it is accepted. Is there any way to restrict cells to disallow formulas
Thanks
 
Derafeda,

Data / Validation / Custom and ISNUMBER() in the formula box.

Notice however that the user can paste over the cell and thereby "destroy"
the conditions.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

derafeda said:
We have a spreadsheet that we would like to restrict cell entries to
Click to expand...
numbers only. We have used conditional formatting and validation and the
user can still enter a formula. If the formula evaluates to a valid entry
it is accepted. Is there any way to restrict cells to disallow formulas?
 
Hi Auk
this won't do if the formula entered also returns a value. E.g.
- cell A1 = 1
- cell A2: Apply the validation with ISNUMBER
now enter the following in A2: =A1 -> no error for this entry

You may try the following (a little bit kludgy):
Put this user defined function in one of your modules
Function Is_Formula(rng as range) as boolean
Is_Formula = rng.hasformula
end function

Normally you would now enter the following formula in the data
validation dialog
=NOT(IS_FORMULA(A2))

But data validation seems to accept onyl build-in functions -> this
does not work. But you can try the following workaround:
- put the following formula in the adjacent column (you may hide this
column later). So in this example in B2
NOT(IS_FORMULA(A2))
- Enter the following formula in the data validation dialog
=B2

This works for me (still curious why data validation does not accept
UDFs)
 
Hi!

SFAIK the validation happens after the caalculation of the cell ha
been triggered. If that is so, then it's the result which is bee
looked at . This shows itself when you try putting the same formula o
calculation in the cell with different validating criteria. Restrictin
to decimal numbers allows you to put in 22*sin(0.5): but you can't i
you insist on integers.

But does it have to matter?

Al
 
Hi derafeda

Try this event in the sheet module

Right click on a sheet tab and choose view code.
Paste the event in there and press Alt-Q to go back to Excel.


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.HasFormula = True Then Target.Value = ""
End Sub


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




derafeda said:
We have a spreadsheet that we would like to restrict cell entries to numbers only. We have used conditional formatting and
Click to expand...
validation and the user can still enter a formula. If the formula evaluates to a valid entry it is accepted. Is there any way
to restrict cells to disallow formulas?
 
Hi Frank,

You are right. Thanks for pointing it out.

I thought I tested it in that way and XL was restricting me there.
But actually the "input" in the formulabox was so faulty that XL did not
only restrict numbers but was restricting any input., which I didn't notice
at that time.

--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *
 
Back
Top