Protect Number Format Only

[Greg Lovern]

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

 

[Gary''s Student]

Hi Greg:

A typical approach is to use some kind of userform for input and have a
macro actually fill the cells.

 

[Gary Keramidas]

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

 

[Greg Lovern]

A typical approach is to use some kind of userform for input and have a
macro actually fill the cells.

Thanks, but data entry on this form is too ad hoc and big for an input
form. There are hundreds of columns, potentially tens of thousands of
rows, and a user might want to paste any size of range (or a single
cell) anywhere, in any order.


Thanks,

Greg

 

[Greg Lovern]

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

Thanks, I tried that but all it does is store the destination number
formatting, and reapply it after the damage is done. Leading and
trailing zeros are lost, and exponential notation is still there. For
example:

In your worksheet with your code behind it, copy the text
00123456789876543210000 (including leading and trailing zeros) from
some other rich-text application such as email or Word, and paste into
Excel in a cell with text formatting. Yes, the cell gets its text
formatting back, but the damage is already done -- what happened to
the leading and trailing zeros? How do I find out how many leading and
trailing zeros there were, so I can replace them?


Thanks,

Greg

 

[Greg Lovern]

BTW I've looked at "Allow Users to Edit Ranges" too, but it turns
protection completely off for the affected cells; it doesn't allow me
to protect number formatting (or formatting in general) while allowing
the user to edit the cell contents.

I'm thinking of trapping Ctrl+v and substituting Paste Values for the
affected worksheet, and trying to educate the users (ha ha -- all
25,000 of them with more to come, yeah right...) to paste with the
keyboard instead of with menu or toolbar.

Greg

 

[Greg Lovern]

I want to protect number format only, while allowing the user to
change the contents of the cells.

Any other suggestions?

We have over 25,000 users and growing, almost all of them outside our
organization. Educating all of them to always do a Paste Values would
be like -- who's the mythological character who spends eternity
pushing a boulder uphill only to have it roll back down each time?

BTW, ignore what I said about trailing zeros; that was a brain fart.
Trailing zeros are obviously not truncated. The problem is leading
zeros.


Thanks for any suggestions.

Greg

 

[BearWolf]

Any other suggestions?

We have over 25,000 users and growing, almost all of them outside our
organization. Educating all of them to always do a Paste Values would
be like -- who's the mythological character who spends eternity
pushing a boulder uphill only to have it roll back down each time?

BTW, ignore what I said about trailing zeros; that was a brain fart.
Trailing zeros are obviously not truncated. The problem is leading
zeros.

Thanks for any suggestions.

Greg

I was suffering with this 0 problem rather you brother.
i want to ware a suicide bomber belt and want to blast inside the
Microsoft Excel developer team hall and kill every bod ;x)