Validation to Prevent formula

srizki

Well-known Member
Joined
Jan 14, 2003
Messages
1,831
Office Version
  1. 365
Platform
  1. Windows
Is there a way to force users to put hard coded number and not formula in any cell.

Some users put a reference to a cell like =D25, rather than hard code number and compare it to =D25 as in this example.

Thanks

Sohail
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
That won't stop the user from putting in a formula if the result of the formula meets the criteria.

Perhaps, this is set for A1:A100, change as necessary:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Range("A1:A100"), Target)
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c.HasFormula Then
            MsgBox "Cell can not contain formula", vbExclamation
            c.ClearContents
        End If
    Next
Application.EnableEvents = True
End Sub

or you could convert it from the formula to the value.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Range("A1:A100"), Target)
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c.HasFormula Then c.Value = c.Value
    Next
Application.EnableEvents = True
End Sub

To put this code in, copy the code above.
Right click on the sheet tab for the sheet you want this to happen on.
Click on View Code
Paste into white area.
Hit Alt-q
The code will be saved with your workbook when you save the workbook
 
Last edited:
Upvote 0
Jerald I tried yours, it did not work, I have not tried Hotpepper's code yet.

thanks
I will let you know when I will try in a few minutes.
 
Upvote 0
Hotpepper,

As for the code for conver to value, worked as it should be on the range I9:I13.
The other code to prevent formula in on same sheet, under the first one on range C95:C101, that gives error "Compile error - "Ambiguous name detected: worksheet-Change", and it highlights the very first line
"Private Sub worksheet-Change (ByVal Targe As Range),
help will be appreciated.

Thanks.
 
Upvote 0
You would need to use one or the other as both methods are a worksheet_change event. So if you put in the prevent formula code you will need to take out the convert code.

Hope that helps.
 
Upvote 0
Thank you schielrn, in fact that was also my question when I was pasting the other code.
Thanks
 
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,249
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top