CODEWITHSUNDEEP
excel
Steven Martin
Steven Martin

Reputation: 3272

Excel Data Validation - Dont allow Formulas

Is there any way of using Data Validation to stop someone entering a Formula and only allow text?

I don't want somebody using a formula to search very hidden sheets because they will know the names of these sheets.

I know excel is not secure, but I don't want someone to easily read these sheets with just =SHEET!A1

I have tried custom validation

  =ISERROR(FIND("=",A1))

but unfortunately I need to allow them to use the = sign

Upvotes: 1

Views: 1595

Answers (2)

NAW
NAW

Reputation: 31

Yes - really simple - create a Custom data validation and use formula: =NOT(ISFORMULA(A1))

No need to over-engineer it with VBA, but of course its easily disabled

Upvotes: 3

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

No there is not. The closest you could come would be to write a UDF that detects the presence of a formula; enter that UDF on some cell in the active sheet, and reference that cell in the data validation formula.

However, that would not prevent a user from entering a formula in some other cell, and then copy/paste to the validated cell. Data validation only checks when a formula is entered directly. It does not check that pasted data conforms to the rule(s).

Jeeped's suggestion of using worksheet event code would be the way to go. And you could easily protect the entire sheet from that phenomenon.

Upvotes: 0

Related Questions