What happened when you tried it?



Rick wrote:

Dave Peterson wrote:
Maybe you could just intercept the change. This worked in light testing.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

On Error GoTo errHandler:

myFormulas = Target.Formula
With Application
.EnableEvents = False
.Undo
End With
Target.Formula = myFormulas

errHandler:
Application.EnableEvents = True

End Sub

rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.

(But I couldn't format an unlocked cell on a protected sheet via Format|Cells.)

Rick wrote:

Hi all,

I have used the "EnableSelection = xlUnlockedCells" to Protect some worksheets, but this allows the
cells to be reformatted. What I want are cells that can only have their values changed and not
allow them to have their formatting altered...this includes copying or cutting and pasting.

How can this be accomplished using VBA code in a code segment like:

With Worksheets(Indx)
.Unprotect

' do my stuff here...

.EnableSelection = xlUnlockedCells
.Protect
End With

TIA,

Rick


Thanks Dave,

The problem I had was that a cut and paste would paste the formatting too...I just want to allow
changing the cells value.

Rick

--

Dave Peterson