0

I'm using MS Excel 2007. I would like to restrict the data entry of some cells in a worksheet to certain numeric values and also forbid the entry of formulas in those cells.

As an example, given this data

   |  A |  B |  C
---+----+----+-----
 1 |  4 | 26 |
 2 |    |    |

I would like to only allow literal numeric values greater than 100 to be entered in C1.

I tried using DataData Validation→[Allow=Decimal, Data=Greater than, Minimum=100] but this also allows certain formulas to be inputted. If you try entering =A1*B1 in cell C1, because it evaluates to more than 100, the data validation allows the formula.

How do I enforce this extra no-formula restriction?

  • Write proper Worksheet_Change. Check the range changed, and if it contains restricted cells, undo changes. – Akina Jul 12 '18 at 11:43
0

This is possible without using any VBA. All that is required is a custom data validation and changing the number format of the cell:

Worksheet Screenshot showing the Data Validation Formula

Enter the following as the custom data validation formula for C1:

=VALUE(C1)>100

Then change the number format of the cell to Text:

Worksheet Screenshot showing the Number Format

All attempts to enter a formula will now trigger a data validation error.

Caveats:

You just need to be careful when using the entered values. Calculations should work fine, but comparisons with actual numbers won't work correctly. It is simplest to always wrap the value with a VALUE() function whenever you need to use it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.