Allow data changes but prevent formatting changes

[Quantum]

Is it possible in Excel 97 to protect a spreadsheet in such a way tha
data can be entered in unlocked cells but formatting changes cannot b
made? I have a spreadsheet model set up that works great except i
users copy data from one unlocked cell to another the formatting of th
other cell is copied as well (in this case the borders)

 

[Nick Hodge]

Simply no.

XL 2003 allows selective locking, that is allows users to delete rows,
insert rows, format cells, etc, but it still starts with a premise that the
user cannot enter data in locked cells and unlocked ones are just
that...unlocked, sorry
--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Dave Peterson]

If the user has macros enabled,
then maybe you can catch all changes (including pastes),
keep the formulas just pasted
do an undo (putting the values/formulas back)
and just plop the formulas in.

This might be worth a try:

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.

Then back to excel and try it out.

It seemed to work ok for me. But a few weeks ago, someone said that they
couldn't get it to work right. I don't think that poster followed up--so I'm
not sure what the problem was.