protect cell formatting

  • Thread starter Thread starter David
  • Start date Start date
D

David

Excel 2003 - I have a spreadsheet that has various cells with different types
of formatting - ie: bold / shade / font size etc. When a user copies / pastes
from text from 1 cell to another, it carries over the formatting as well as
the text - I want just the text to be copied.
 
Instead of "regular" Paste - try 'PasteSpecial' > Values' > 'OK'
Micky
 
Yes, thank you. I was hoping for someway to "lock" the formatting, as there
are several users of this spreadsheet - some are not so computer savy.
 
If you are willing to use some VBA try this event code.

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

This is sheet code. Right-click on the sheet tab and "View code"

Copy/paste the code into that sheet module.

Alt + q to return to Excel.

When you copy and paste, the destination formatting will be
retained...........source formatting is ignored.

Requires users to enable macros when opening the workbook.


Gord Dibben MS Excel MVP
 
Back
Top