Lock formatting
 

Hi, how can I set up a cell/group of cells so that the data in them can be
changes but the formatting cannot. For instance if I put the number
formatting as a percentage to 5 decimal places, then even if someone
copy/pastes something from a different cell that is not in that format, the
cell will take the new input without taking the formatting...

Thanks
HF

Lock formatting
 

Copy and paste only........

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


Gord Dibben MS Excel MVP

On Thu, 25 Oct 2007 15:54:00 -0700, hfazal
wrote:

Hi, how can I set up a cell/group of cells so that the data in them can be
changes but the formatting cannot. For instance if I put the number
formatting as a percentage to 5 decimal places, then even if someone
copy/pastes something from a different cell that is not in that format, the
cell will take the new input without taking the formatting...

Thanks
HF

Lock formatting
 

Not sure how to apply this?

"Gord Dibben" wrote:

Copy and paste only........

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


Gord Dibben MS Excel MVP

On Thu, 25 Oct 2007 15:54:00 -0700, hfazal
wrote:

Hi, how can I set up a cell/group of cells so that the data in them can be
changes but the formatting cannot. For instance if I put the number
formatting as a percentage to 5 decimal places, then even if someone
copy/pastes something from a different cell that is not in that format, the
cell will take the new input without taking the formatting...

Thanks
HF

Lock formatting
 

Right-click on the sheet tab and "View Code"

Copy/paste into that sheet module.


Gord

On Fri, 7 Dec 2007 17:19:00 -0800, hfazal
wrote:

Not sure how to apply this?

"Gord Dibben" wrote:

Copy and paste only........

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


Gord Dibben MS Excel MVP

On Thu, 25 Oct 2007 15:54:00 -0700, hfazal
wrote:

Hi, how can I set up a cell/group of cells so that the data in them can be
changes but the formatting cannot. For instance if I put the number
formatting as a percentage to 5 decimal places, then even if someone
copy/pastes something from a different cell that is not in that format, the
cell will take the new input without taking the formatting...

Thanks
HF