Prevent formatting Cells with Protection

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
 
D

Dave Peterson

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.)
 
R

Rick

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
 
D

Dave Peterson

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...
 
R

Rick

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
 
R

Rick

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
 
D

Dave Peterson

I thought that the code I provided did that.

I just tried it again and it worked (again) for me.

try adding a message box in the code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myFormulas As Variant

MsgBox "I made it here!"

On Error GoTo errHandler:

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

errHandler:
Application.EnableEvents = True

End Sub

When you make a change, do you get the message box?

If yes, then I don't have another suggestion.

If no, then you may have put the code in the wrong location (it belongs under
the worksheet).

Or you may have macros disabled or you may have disabled event processing.

I'm still guessing the wrong location.

The code remembers the formulas (myformulas = target.formula). Then it does an
undo so it's back to its previous state. Then it assigns the formulas--just the
formulas--no formatting.
 
Top