Formatting not protected when paste

  • Thread starter Thread starter Greg Lovern
  • Start date Start date
G

Greg Lovern

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
 
I've tested the workbook solution on Excel 2003, but while it works as advertised it doesn't solve the entire problem.

1. Format a cell for Accounting format with dollar sign.

3. Put a number into the cell to confirm that the dollar sign is left justified.

4. Go away and get coffee. On return, type $230 into the cell.

5. You'll see that the cell format has been changed from Accounting to Currency, although nothing is selected in the cell format number tab.

So, the solution prevents change by pasting, but does not prevent change by simply typing into the cell.

Help?
 
Based on http://www.ozgrid.com/VBA/run-macros-change.htm I think a potential solution is to add this code to the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub

This will enforce accounting format upon changing the cell by typing. I did not test for pasting.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Dynamic Data Controls with Entity Framework
http://www.eggheadcafe.com/tutorial...amic-data-controls-with-entity-framework.aspx
 
Based on http://www.ozgrid.com/VBA/run-macros-change.htm I think a potential solution is to add this code to the sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveCell.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
End Sub

This will enforce accounting format upon changing the cell by typing. I did not test for pasting.

Submitted via EggHeadCafe - Software Developer Portal of Choice
Excel Tips: Net Present Value (NPV) and Internal Rate of Return (IRR)
http://www.eggheadcafe.com/tutorial...alue-npv-and-internal-rate-of-return-irr.aspx
 
This does seem to work:
Private Sub Worksheet_Change(ByVal Target As Range)
'ENFORCE ACCOUNTING FORMAT IN CELL I54
'Do nothing if more than one cell is changed or if content is deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$I$54" Then
'First make sure target is a number
If IsNumeric(Target) Then
'Stop potential runtime errors and halting code
'On Error Resume Next
'Turn off all events so operation does not put the code into a loop
Application.EnableEvents = False
'HERE FOLLOWETH THE CODE WE'RE AFTER
Target.Cells.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
'Turn events back on
Application.EnableEvents = True
'Allow runtime errors again
On Error GoTo 0
End If
End If
End Sub



Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET 4.0 browser capabilities
http://www.eggheadcafe.com/tutorial...af0bf4d1b/aspnet-40-browser-capabilities.aspx
 
Dear Greg,
your event code is the closest to a soulution to my problem as I have seen so far. And I have been searching for a long time? I was hoping you could help me to cross the finish line.
My problem is to get the format ?break row? (I have a Swedish office version so I hope my translation is correct) to stick when pasting text in an excel cell.
Your code seems to do the trick, but with a small problem:
If I paste a text in cell A1 the break-row formatting will not kick in until I paste something in another cell on the same row. In other words this is what I do and the result I get:
Step 1: By using ?format cell? I set the cell to enable ?break row?.
Step 2: Paste text (?Hi my name is Josephine and I am in great need of help?) in cell A1
Result of step 2: Text shows up in a single row, stretching over other cells in the same row
Step 3: Paste text (?Hi my name is Josephine and I am in great need of help?) in cell B1
Result of step 3: The text in both cell A1 and B1 are now with broken rows, looking something like this:
Hi my name is Hi my name is
Josephine and Josephine and
I am in great I am in great
need of help need of help
To summarize: It seems your code works only the second time you use it on a row, but then it works retrospective and changes the look of the first cell as well.
My question to you is: Is there a way to modify the code to make it work the first time? That would really make my day/year!
The reason I am wondering is that I need it to work for others, with less excel experience, to prevent them from refusing to use a spreadsheet I really really need them to use. And they will only paste once on every row and need to see right away that their text has not ?disappeared?.
I am using excel 2010.
Kind regards
/Josephine

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
On Wednesday, October 15, 2008 7:50 PM Gord Dibben wrote:
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
Click to expand...
//www.ozgrid.com/VBA/run-macros-change.htm[/URL] I think a potential solution is to add this code to the sheet:



Private Sub Worksheet_Change(ByVal Target As Range)

ActiveCell.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

End Sub



This will enforce accounting format upon changing the cell by typing. I did not test for pasting.
Click to expand...
Click to expand...
//www.ozgrid.com/VBA/run-macros-change.htm[/URL] I think a potential solution is to add this code to the sheet:



Private Sub Worksheet_Change(ByVal Target As Range)

ActiveCell.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"

End Sub



This will enforce accounting format upon changing the cell by typing. I did not test for pasting.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top