I have a protected sheet where the protect properties I have set allow users
only to select unlocked cells. However users can change the formulas of
locked cells by ragging unlocked cells which the locked cells reference.

Can I stop this?

Thanks in advance.

Angus,

You are so right. Cell drag and drop is bad news at data entry time, and
should never be used with or without the protection mechanism. It's for
design time only, wherever there are formulas at play. There seems to be no
protection that will prevent this. I've always told clients either to not
tell data entry folks about it, or to tell them not to use it. If it's
inadvertent clobbering of formulas via drag and drop, you could prevent it
manually with Tools - Options - Edit - Allow dell drag and drop (turn off),
or automatically with this in the ThisWorkbook module:

Private Sub Workbook_Open()
Application.CellDragAndDrop = False
End Sub

This won't stop deliberate formula damage from the determined hackers --
they can just turn it back on manually. Also, it's an application-wide
setting -- it will affect other sheets in the workbook, and other open
workbooks (Excel doesn't handle multiple open workbooks well). It's not
stored with workbooks when saved, like the dopey calculation auto/manual
setting. You could turn it back on automatically when the workbook is
closed with:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CellDragAndDrop = True
End Sub

If there are to be multiple workbooks open concurrently, you could instead
do this in WindowActivate and WindowDeactivate subs, but this presumes all
the other workbooks have the CellDragAndDrop turned on. Otherwise, you'll
mess them up. It seems endless.

All this applies to Excel 2002 and prior. I don't know if it's been
addressed in a later version. I kind of doubt it, but you could look into
it. Or maybe someone else will come forward with a solution. I'm going to
get another cup of coffee. Ranting consumes energy.
--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Angus" wrote in message
...
I have a protected sheet where the protect properties I have set allow
users
only to select unlocked cells. However users can change the formulas of
locked cells by ragging unlocked cells which the locked cells reference.

Can I stop this?

Thanks in advance.

Is the data so wide and varried that it rules out the use of data validation?
------------------------------------------------------------------------------------



wrote:

I have a protected sheet where the protect properties I have set allow users
only to select unlocked cells. However users can change the formulas of
locked cells by ragging unlocked cells which the locked cells reference.

Can I stop this?

Thanks in advance.