How can I stop the format changing when I copy data into an unlocked cell in
a format protected worksheet?

I need to allow people to both enter data or copy data from another source
into cells so I have unlocked these cells but also don't want the format of
the cells to be changed. If you enter data directly the format does not
change but if you copy and paste data it changes the format.

I know you can use "copy paste special values" but would like to know if
there is some system way of doing this.

Copy/paste this into the sheet module.

Right-click on sheet tab and "View Code" to open the module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myValue
With Application
.EnableEvents = False
myValue = Target.Formula
.Undo
Target.Formula = myValue
.EnableEvents = True
End With
Application.CutCopyMode = False
End Sub


Gord Dibben MS Excel MVP


On Wed, 10 Feb 2010 21:35:01 -0800, Linty
wrote:

How can I stop the format changing when I copy data into an unlocked cell in
a format protected worksheet?

I need to allow people to both enter data or copy data from another source
into cells so I have unlocked these cells but also don't want the format of
the cells to be changed. If you enter data directly the format does not
change but if you copy and paste data it changes the format.

I know you can use "copy paste special values" but would like to know if
there is some system way of doing this.

This is an excellent solution that solved a big problem I was having, too - thank you!!

Unfortunately it's caused a smaller problem: it disables the Undo/Redo buttons - something I think my users won't appreciate. Is there anyway to prevent the formatting from changing without disabling Undo/Redo?

Gord

Does this need to be pasted into the sheet that has the target data where ifo is copy and pasted regularly or does it need to be pasted into the sheet with formulae referencing the data.

Also will it only wotj after the sheet is re-opened.