Protect method question

[Dan E]

I get problems when users accidentally move or cut data cell contents to
which formulas refer. Is there a way to lock cells that allows the user to
clear the contents and change the cell contents to something else, but
prevents them from cutting or moving the cell?

 

[Mohamed Shafiee]

Hi there,

It is easy. Just follow two easy steps.

1. Format -> Cells -> Protection Check the Locked checkbox.
2. Tools -> Protection -> Protect Sheet Click ok.

Shafiee.

 

[Mohamed Shafiee]

Oooh... Sorry about that.

You can use a macro to prevent the users from deleting the cell. Something
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
On Error GoTo errorhandler
a = Target.Name
End If

Exit Sub

errorhandler:
MsgBox "test"
Application.Undo


End Sub

 

[Dan E]

Mohamed - sorry, I don't see how that macro would do anything. What I want
to do is prevent users from MOVING, CUTTING or DELETING cells (which would
cause any reference to that cell to give an error), but allow them to clear
the contents of the cell and enter new values (in other words, be able to
edit the cell).

Thanks,

Dan

Oooh... Sorry about that.

You can use a macro to prevent the users from deleting the cell. Something
like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And Target.Column = 1 Then
On Error GoTo errorhandler
a = Target.Name
End If

Exit Sub

errorhandler:
MsgBox "test"
Application.Undo


End Sub

 

[Mohamed Shafiee]

Hi Dan E,

What my macro does is, it prevents users from deleting cell A1. It does it
by checking the name of the cells which change in the worksheet, and if the
name property generates an error, undoes the last action.

It can be modified so that cell A1 can't have a blank value, thus disbling
the users from cutting that cell.

Shafiee.

Mohamed - sorry, I don't see how that macro would do anything. What I
want to do is prevent users from MOVING, CUTTING or DELETING cells (which
would cause any reference to that cell to give an error), but allow them
to clear the contents of the cell and enter new values (in other words, be
able to edit the cell).

Thanks,

Dan

 

[Ken Wright]

One possible option:-

Assuming for example that you had cells F10 and F11 that people edited, but
as you say if they move/cut etc it screws things up. Instead of having them
edit those cells, have two other input cells, eg A1 and A2 that represent
the data that should be in F10 and F11, and then use INDIRECT in F10 and F11
to refer out to A1 and A2, eg

In cell F10 have =INDIRECT("A1")
In cell F11 have =INDIRECT("A2")

Leave A1 and A2 as editable and lock the rest of the sheet down with
protection. They can generally do whatever the hell they like to A1 and A2,
they will not break the link in the formulas in F10 and F11.

 

[Ken Wright]

Actually, I guess what I should have said was - Just use the INDIRECT
function within the other cells that reference the cells they can edit, and
then protect the sheet and workbook.

 

[Dan E]

Ken - excellent idea, thanks. As is, implementiation would be difficult
with the way the sheet and formulas are structured, the multiple versions
that exist for different parts of the health care facility etc. Certainly
could be done, but would in essence need a complete rebuild of the sheet.
Which leads me to speculate if it would be possible to have a second, data
entry sheet, which automatically mirrors the structure of the first (the
sheet with formulas, macro and hard-to-fiddle-with structure), use the new
sheet as a data input sheet, and have INDIRECT formulas in the original data
cells in the first (original) sheet. What thinkest thou? The original
sheet currently stays the same in column structure (data column representing
a date, then two hidden formula columns, repeat to give a 2-week period),
but users can and do add rows. Formulas and macro can handle that.

TIA,

Dan

 

[Dan E]

OK - thanks, Mohamed.

Dan

Hi Dan E,

What my macro does is, it prevents users from deleting cell A1. It does it
by checking the name of the cells which change in the worksheet, and if
the name property generates an error, undoes the last action.

It can be modified so that cell A1 can't have a blank value, thus disbling
the users from cutting that cell.

Shafiee.