Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
Hi Greg:
A typical approach is to use some kind of userform for input and have a macro actually fill the cells. -- Gary''s Student - gsnu2007g "Greg Lovern" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
On Mar 27, 1:00 pm, Gary''s Student
wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
On Mar 27, 1:18 pm, "Gary Keramidas" <GKeramidasATmsn.com wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
On Mar 27, 12:32 pm, Greg Lovern wrote:
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
On Mar 27, 12:32 pm, Greg Lovern wrote:
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protect Number Format Only
On Apr 8, 11:31 am, Greg Lovern wrote:
On Mar 27, 12:32 pm, Greg Lovern wrote: 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 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protect cell format | Excel Discussion (Misc queries) | |||
Protect Excel Format Only | Excel Discussion (Misc queries) | |||
Protect Number Format Only | Excel Discussion (Misc queries) | |||
Protect Format OF Worksheet | Excel Worksheet Functions | |||
Number format based on number format of another cell in another workbook | Excel Programming |