Validation to Prevent formula

[srizki]

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

 

[Gerald Higgins]

How about using Data Validation, and then one of the Whole Number or Decimal options ?

 

[Scott Huish]

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:

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.

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

 

[srizki]

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.

 

[srizki]

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.

 

[schielrn]

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.

 

[srizki]

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