Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
I want to protect number format only, while allowing the user to
change the contents of the cells. Or, it would also work if I could protect all formatting while allowing cell contents to be edited. 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 looked at Allow Users to Edit Ranges, but it turns off protection for the affected cells; it doesn't allow me to protect number formatting (or all formatting) while allowing the user to edit the cell contents. 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. Any suggestions on how I can protect number formatting, or formatting in general, while allowing the cell contents to be edited? Thanks, Greg |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
Why not use the worksheet_change event to reapply the formats? -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Greg Lovern" wrote in message I want to protect number format only, while allowing the user to change the contents of the cells. Or, it would also work if I could protect all formatting while allowing cell contents to be edited. 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 looked at Allow Users to Edit Ranges, but it turns off protection for the affected cells; it doesn't allow me to protect number formatting (or all formatting) while allowing the user to edit the cell contents. 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. Any suggestions on how I can protect number formatting, or formatting in general, while allowing the cell contents to be edited? Thanks, Greg |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
Hi Jim,
By the time the worksheet_change event is triggered, the damage is already done. Then, I don't know whether there had originally been any leading and/or trailing zeros or how many, so I don't know how many, if any, to put back. Simply reapplying the text format is easy, but it's not enough. I also need to prevent or fix the loss of leading and/or trailing zeros. Thanks, Greg On Mar 28, 6:27 pm, "Jim Cone" wrote: Why not use the worksheet_change event to reapply the formats? -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Greg Lovern" wrote in message I want to protect number format only, while allowing the user to change the contents of the cells. Or, it would also work if I could protect all formatting while allowing cell contents to be edited. 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 looked at Allow Users to Edit Ranges, but it turns off protection for the affected cells; it doesn't allow me to protect number formatting (or all formatting) while allowing the user to edit the cell contents. 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. Any suggestions on how I can protect number formatting, or formatting in general, while allowing the cell contents to be edited? Thanks, Greg |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
Here's an example of the problem:
In some other rich-text application such as email or Word (but not Excel), enter the text 00123456789876543210000 (including leading and trailing zeros). Then, copy it and paste it into Excel, in a cell that has been formatted as text. Yes, you can easily change the formatting back to text, 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 On Mar 31, 8:50 am, Greg Lovern wrote: Hi Jim, By the time the worksheet_change event is triggered, the damage is already done. Then, I don't know whether there had originally been any leading and/or trailing zeros or how many, so I don't know how many, if any, to put back. Simply reapplying the text format is easy, but it's not enough. I also need to prevent or fix the loss of leading and/or trailing zeros. Thanks, Greg On Mar 28, 6:27 pm, "Jim Cone" wrote: Why not use the worksheet_change event to reapply the formats? -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Greg Lovern" wrote in message I want to protect number format only, while allowing the user to change the contents of the cells. Or, it would also work if I could protect all formatting while allowing cell contents to be edited. 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 looked at Allow Users to Edit Ranges, but it turns off protection for the affected cells; it doesn't allow me to protect number formatting (or all formatting) while allowing the user to edit the cell contents. 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. Any suggestions on how I can protect number formatting, or formatting in general, while allowing the cell contents to be edited? Thanks, Greg |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
On Mar 27, 1:05 pm, Greg Lovern wrote:
I want to protect number format only, while allowing the user to change the contents of the cells. Or, it would also work if I could protect all formatting while allowing cell contents to be edited. 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
Sisyphus
Gord Dibben MS Excel MVP On Tue, 8 Apr 2008 11:30:36 -0700 (PDT), Greg Lovern wrote: who's the mythological character who spends eternity pushing a boulder uphill only to have it roll back down each time? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect Number Format Only
On Mar 31, 11:50*pm, Greg Lovern wrote:
Hi Jim, By the time the worksheet_change event is triggered, thedamageis already done. Then, I don't know whether there had originally been any leading and/or trailing zeros or how many, so I don't know how many, if any, to put back. Simply reapplying the text format is easy, but it's not enough. I also need to prevent or fix the loss of leading and/or trailing zeros. Thanks, Greg On Mar 28, 6:27 pm, "Jim Cone" wrote: Why not use the worksheet_change event to reapply the formats? -- Jim Cone San Francisco, USAhttp://www.realezsites.com/bus/primitivesoftware (ExcelAdd-ins /ExcelProgramming) "Greg Lovern" wrote in message I want to protect number format only, while allowing the user to change the contents of the cells. Or, it would also work if I could protect all formatting while allowing cell contents to be edited. 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 looked at Allow Users to Edit Ranges, but it turns off protection for the affected cells; it doesn't allow me to protect number formatting (or all formatting) while allowing the user to edit the cell contents. 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 damageis already done and I don't know if there were leading zeros or how many. Any suggestions on how I can protect number formatting, or formatting in general, while allowing the cell contents to be edited? Thanks, Greg- Hide quoted text - - Show quoted text - Hi, Do you need to recover your damaged xls file? If so, you can try a tool called Advanced Excel Repair. I have used it to repair many corrupt Excel xls files on my damaged disks successfully. Its homepage is http://www.datanumen.com/aer/ Hope this will help. Alan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I protect cell numbering format ($) but allow editing? | Excel Discussion (Misc queries) | |||
In excel how do I protect the format of a cell but let the user c. | Excel Discussion (Misc queries) | |||
How can I protect only format of the cell ? | Excel Discussion (Misc queries) | |||
Protect Format OF Worksheet | Excel Worksheet Functions | |||
protect format - page setup | Excel Discussion (Misc queries) |