Prevent automatic format change from general to e.g. percentage

Question

My spreadsheet is locked so that users can edit values but not change the format of cells. The cells have type 'general' but there is data validation to ensure the input is numeric.

Despite the locking it is possible to change the format of the cell by entering particular values. For example, entering 4% changes the format to percentage, entering £4 changes the type to currency etc.

I want to prevent this because a) the stored value of the cell may have changed, e.g. 0.04 instead of 4 and b) it's now impossible for the user to change the format back.

I could prevent this by setting the cell type to numeric rather than general. However, this is also undesirable as it ties me to displaying a fixed number of decimal places. I want both '4' and '4.256' to be displayed as such, and there doesn't seem to be a way to do that without either adding trailing zeros to the former (4.00) or rounding the latter (4.26).

Please could someone tell me how to prevent the automatic formatting change, or failing that how to set a numeric format that does what I want.

Answer 1

IMO, the only way to achieve this to use a macro that reset the format to it's original state.

Open the VBA editor (All-F11) and double click on the sheet you want to have this functionality.

Then paste this code in the main window:

Option Explicit

Private Const mcStrPasswort As String = "" '<---Change to your password

Private mStrFormat As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim blnProtected As Boolean
    If mStrFormat = "" Then mStrFormat = "General"
    If Me.ProtectContents Then
        blnProtected = True
        Me.Unprotect mcStrPasswort
    Else
        blnProtected = False
    End If

    Target.NumberFormat = mStrFormat

    If blnProtected Then
        Me.Protect mcStrPasswort
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    mStrFormat = Target.Cells(1, 1).NumberFormat
End Sub

Done!