this may be completely off the wall, but it semed to work in my "very limited"
testing. it may be flawed, but give it a try.
just paste in on the code for the sheet in question, not a general module.

Option Explicit
Public onf As String
Public nnf As String
Private Sub Worksheet_Change(ByVal Target As Range)
nnf = Target.NumberFormat
Debug.Print nnf
If onf = nnf Then
Exit Sub
Else
ActiveCell.NumberFormat = onf
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
onf = ActiveCell.NumberFormat
Debug.Print onf
End Sub


--


Gary


"Greg Lovern" wrote in message
...
I want to protect number format only, while allowing the user to
change the contents of the cells.

We have a workbook used by around (I'm told) 25,000 people, with many
more to come, which is formatted as text. Users paste numbers into it,
and it must preserve leading zeros, and must not convert to
exponential notation.

But users often paste from other formatted sources such as email,
which converts the cell to General number format, losing leading zeros
and converting to exponential notation.


Worksheet protection doesn't seem to allow editing locked cell
contents even with all the restrictions turned off, and of course
worksheet protection doesn't apply at all to unlocked cells.

I thought about using the Worksheet Change event to trap all pastes
and enter them as text, but by the time that Event is triggered, the
damage is already done and I don't know if there were leading zeros or
how many.

Thanks for any suggestions.


Thanks,

Greg