If you protect a sheet's formatting, so that you can't change the formatting
with the menus, Excel still allows changing the number formatting by pasting
in from a source with different formatting. For example:

-- In Excel 2007, create a new workbook.
-- On Sheet1, select the whole sheet.
-- Right-click anywhere in the sheet and choose Format Cells | Number, and
select Text.
-- With the Format Cells dialog still open, select the Protection tab,
Uncheck "Locked", and click OK.
-- Right-click the sheet tab, and choose Protect Sheet. Make sure that
"Format cells", "Format columns", and "Format Rows" are all unchecked. Click
OK.
-- Type "001" (without quotes) in any cell, to verify that the leading zeros
are preserved due to the Text number formatting.
-- Verify that you cannot change the formatting with the menus (Format Cells
is disabled).
-- Go to Sheet2. Enter the number 5 in any cell on Sheet2. Change that
cell's font to bold and red. Copy that cell.
-- Go back to Sheet1. Paste the 5 into any cell other than the cell that
still has the "001" in it.
-- Note that the pasted 5 is bold and red, indicating that formatting was
not protected.
-- Enter "001" into the pasted cell, replacing the 5. Note that the leading
zeros are truncated.
-- Unprotect Sheet1. Right-click the pasted cell and note that the number
formatting has been changed from Text to General.

Also -- if you paste in from a rich-text source outside of Excel such as an
Outlook email, a web page, or a Word document, the number formatting of the
pasted range also changes from Text to General.


Why does this happen?

Is there a workaround that doesn't require (a) educating my tens of
thousands of users on this project to always paste-special/values, or (b)
preventing users from pasting at all and instead providing dialogs for all
input, which, no matter how well executed, would cause an uproar from users
and would not be accepted?

Greg

Greg

Such are the vagaries of Excel<g

You can retain formatting on a copy and paste with this event code in the
sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
'retain formatting when a cell is pasted over with copy.
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub

If you want the event for every sheet in a workbook use this in Thisworkbook
module.

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Range)
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Value
.Undo
Target = myValue
.EnableEvents = True
End With
End Sub


Gord Dibben MS Excel MVP

On Wed, 15 Oct 2008 12:07:01 -0700, Greg Lovern
wrote:

If you protect a sheet's formatting, so that you can't change the formatting
with the menus, Excel still allows changing the number formatting by pasting
in from a source with different formatting. For example:

-- In Excel 2007, create a new workbook.
-- On Sheet1, select the whole sheet.
-- Right-click anywhere in the sheet and choose Format Cells | Number, and
select Text.
-- With the Format Cells dialog still open, select the Protection tab,
Uncheck "Locked", and click OK.
-- Right-click the sheet tab, and choose Protect Sheet. Make sure that
"Format cells", "Format columns", and "Format Rows" are all unchecked. Click
OK.
-- Type "001" (without quotes) in any cell, to verify that the leading zeros
are preserved due to the Text number formatting.
-- Verify that you cannot change the formatting with the menus (Format Cells
is disabled).
-- Go to Sheet2. Enter the number 5 in any cell on Sheet2. Change that
cell's font to bold and red. Copy that cell.
-- Go back to Sheet1. Paste the 5 into any cell other than the cell that
still has the "001" in it.
-- Note that the pasted 5 is bold and red, indicating that formatting was
not protected.
-- Enter "001" into the pasted cell, replacing the 5. Note that the leading
zeros are truncated.
-- Unprotect Sheet1. Right-click the pasted cell and note that the number
formatting has been changed from Text to General.

Also -- if you paste in from a rich-text source outside of Excel such as an
Outlook email, a web page, or a Word document, the number formatting of the
pasted range also changes from Text to General.


Why does this happen?

Is there a workaround that doesn't require (a) educating my tens of
thousands of users on this project to always paste-special/values, or (b)
preventing users from pasting at all and instead providing dialogs for all
input, which, no matter how well executed, would cause an uproar from users
and would not be accepted?

Greg