Hello:

Is there a way to lock from allowing to move cells? With VBA code?

(whenever someone moves a cell, it causes the formulas to go nuts and loses
its references and displays #REF, so I want to block from the user from
moving cells)

Please help.

Thanks.

You can lock all the cells in the worksheet, protect the sheet and
specify that the user can select only unlocked cells (all via the
Protect Sheet dialog under Protection on the Tools menu).

That said, if merely changing the selection cause formulas to
misbehave, then I think you have more serious problems. Well written
formulas should be entirely agnostic about the selected cell. If you
are calling from worksheet cells functions written in VBA, note that
those functions should not reference "ActiveCell" or "Selection". If a
function needs to know whence it was called, it should use
Application.Caller or Application.ThisCell.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Sun, 12 Oct 2008 08:00:01 -0700, art
wrote:

Hello:

Is there a way to lock from allowing to move cells? With VBA code?

(whenever someone moves a cell, it causes the formulas to go nuts and loses
its references and displays #REF, so I want to block from the user from
moving cells)

Please help.

Thanks.

My problem was not changing the selection, only when one selects a cell and
drags and drops it somewhere else then the formulas go #REF.

I think the correct way to lock it is by using the CellDragAndDrop. Thanks
anyways.




"Chip Pearson" wrote:

You can lock all the cells in the worksheet, protect the sheet and
specify that the user can select only unlocked cells (all via the
Protect Sheet dialog under Protection on the Tools menu).

That said, if merely changing the selection cause formulas to
misbehave, then I think you have more serious problems. Well written
formulas should be entirely agnostic about the selected cell. If you
are calling from worksheet cells functions written in VBA, note that
those functions should not reference "ActiveCell" or "Selection". If a
function needs to know whence it was called, it should use
Application.Caller or Application.ThisCell.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email is on the web site)
USA Central Daylight Time (-5:00 GMT)


On Sun, 12 Oct 2008 08:00:01 -0700, art
wrote:

Hello:

Is there a way to lock from allowing to move cells? With VBA code?

(whenever someone moves a cell, it causes the formulas to go nuts and loses
its references and displays #REF, so I want to block from the user from
moving cells)

Please help.

Thanks.