Locking a cell's format, but not value
 

Hello,

I did some searches and although I found some other threads discussing
this, I found no working solution. I suspect what I want cannot be
done, but I am asking anyway in case there is a solution that was
missed or perhaps a method became available in later versions of Excel
or via patches.

I would like to lock or freeze the format of a cell, so that a user can
copy a value from another cell to it, without the format also being
copied. I realize this can be done with 'Paste Special', but I find
that method too cumbersome to expect my users to use.
I am using Excel 2003 (11.6113.5703).

Thanks,
Tim

You can use an event macro to reset the format whenever the cell value
changes. For instance:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("C2, J9")) Is Nothing Then
With Range("C2, J9")
.NumberFormat = "0.000_);[Red](0.000)"
.Interior.ColorIndex = 34
End With
End If
End Sub

Put this in the worksheet code module (right-click the worksheet tab and
choose View Code).

This won't keep a user from directly changing the format, but it will
reset it if another value is entered, either directly or with copy/paste.


In article .com,
"Tim Laplaca" wrote:

I did some searches and although I found some other threads discussing
this, I found no working solution. I suspect what I want cannot be
done, but I am asking anyway in case there is a solution that was
missed or perhaps a method became available in later versions of Excel
or via patches.

I would like to lock or freeze the format of a cell, so that a user can
copy a value from another cell to it, without the format also being
copied. I realize this can be done with 'Paste Special', but I find
that method too cumbersome to expect my users to use.
I am using Excel 2003 (11.6113.5703).

That's probably a pretty good solution, I'll give it a try. Thanks!