restrict user from entering formula

[MN Kim]

Hi - I want my users to enter a whole number value rather than a formula in a fixed asset spdsht. They are to type in their ending balance and then distribute it (additions, retirements, etc.). Then I have a formula to notify them if they have an undistributed balance. But some of them are entering a formula to sum their ending balance rather than entering it and distributing it.
I've tried data validation to restrict them from entering a formula which seems to work except if they have no activity that month and their beginning balance equals their ending. Then it won't allow them to enter the ending balance--because it is the result of the formula I restricted??
Confusing enough?
Thanks for any help you can provide!

 

[BrianB]

This might work. It checks each entry for the formula = sign at the beginning and cancels the entry if necessary.

Goes in sheet module. right click tab & "View code".

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim Entry As String
    Entry = Target.Formula
    If Left(Entry, 1) = "=" Then
        MsgBox ("Cannot enter a formula")
        Target.Value = ""
    End If
End Sub

 

[tusharm]

Aren't you distorting the definition of an ending balance? It, after all, is the balance that is the end product of various activities. Consequently, it should equal opening balance + inflows - outflows. To me the concept of "enter an ending balance and then distribute it" is totally alien.

Hi - I want my users to enter a whole number value rather than a formula in a fixed asset spdsht. They are to type in their ending balance and then distribute it (additions, retirements, etc.). Then I have a formula to notify them if they have an undistributed balance. But some of them are entering a formula to sum their ending balance rather than entering it and distributing it.
I've tried data validation to restrict them from entering a formula which seems to work except if they have no activity that month and their beginning balance equals their ending. Then it won't allow them to enter the ending balance--because it is the result of the formula I restricted??
Confusing enough?
Thanks for any help you can provide!

 

[MN Kim]

Yes, the ending balance should be beg bal + inflows - outflows. But I am receiving information from over 34 countries across the globe. Invariably when one of them changes the worksheet to a sum formula rather than entering the ending balance the sum of the activity does not equal the ending balance in their general ledger--i.e. they haven't correctly entered their inflows and outflows.
If I can require them to key in their ending balance I have a formula that notifies them if they haven't fully distributed it. Not perfect but has worked for most people.
Thanks Brian, I'll try your solution!