Posted to microsoft.public.excel.programming
|
|
Prevent formatting Cells with Protection
Are you sure you put it in the correct spot--under that worksheet.
rightclick on the worksheet tab that should have this behavior. Select View
code and paste this in.
Rick wrote:
Dave Peterson wrote:
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,
The code you gave me allowed a value to be pasted into a cell along with changing the "pattern"
format to match the origin. There are no formulas in the cells, they simply provide values for some
other formulas. The pattern color is important as it is used to separate columns
Rick B
--
Dave Peterson
|