Prevent formatting Cells with Protection

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

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
 
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.)
 
Dave said:
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.)
Click to expand...
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
 
What happened when you tried it?


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
Click to expand...
 
Dave said:
What happened when you tried it?
Click to expand...
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 said:
I just hope it works as well as George's solutions!
Click to expand...
I guess something happened to my last reply...hmmm?

Well Dave, the code did nothing. The values and the formatting changed when I used a copy and paste.
Is there a way to trap a paste event and turn it into a PasteSpecial "Values Only"?

Rick
 
Back
Top