I want to prevent users from MOVING, CUTTING or DELETING data 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). Any suggestions VERY welcome.

TIA,

Dan


--
Dan E

NOT SURE this will be enought, but it could be a starter or an idea:
this allow the double-click action only in the A1 cell: so user can
edit it (only using the double-click)




Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("A1")) Is Nothing Then

ActiveSheet.Unprotect 'editing will be permitted
Cancel = False 'doubleclick action is maintain

Else
protection 'see below, this reactivate the protection

' Cancel = True
'do cancel only if you want to stop the "unprotect sheet first" dialog

End If
End Sub


Private Sub protection()
ActiveSheet.Protect UserInterfaceOnly:=True
'the user only can not change the Sheet;VBA still can
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
protection 're-established the protection after any change
End Sub

Private Sub Worksheet_Activate()
protection
End Sub

Thanks, D.2. Does this mean that, if I lock the data cells that I want to
make editable but not movable, cuttable or deletable, then apply protection
to the sheet, the locked cells can still be edited by double-clicking? Or
does it have to be the way you've done it below? I can ALMOST see how your
subs work, but am something of a novice with VBA syntax, methods etc. - any
more guidance MUCH appreciated.

TIA,

Dan
"D.2" wrote in message
...
NOT SURE this will be enought, but it could be a starter or an idea:
this allow the double-click action only in the A1 cell: so user can edit
it (only using the double-click)




Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)

If Not Intersect(Target, Range("A1")) Is Nothing Then

ActiveSheet.Unprotect 'editing will be permitted
Cancel = False 'doubleclick action is maintain

Else
protection 'see below, this reactivate the protection

' Cancel = True
'do cancel only if you want to stop the "unprotect sheet first" dialog

End If
End Sub


Private Sub protection()
ActiveSheet.Protect UserInterfaceOnly:=True
'the user only can not change the Sheet;VBA still can
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
protection 're-established the protection after any change
End Sub

Private Sub Worksheet_Activate()
protection
End Sub

more guidance MUCH appreciated.


Well, first we test if this is your special cell:
IT IS - you unprotect the sheet, so Excel will allow the edition

IT IS NOT - the sheet stay (or is again) protected, so locked cells
will not be edited

if you still want to edit unlocked cells, do not use "cancel=true" in
the ELSE statment: the user will have the error message on (not special)
locked cells and will edit unlocked ones.


When a locked cell, but special one, is edited, then the 'Change' sub is
excecuted: we always re-activate the protection after any change.
(remember with the special cell we unprotected the sheet, but we want
the protection back after the change !) So this will take effect with
any change, for any cell: doesn't matter for unlocked cells, but is
mandatory after our special cell "unprotection"


the "activate" sub make the sheet protected at the opening


if you need more than one special cell, you can Name the cells-zone,
or add some other cells in VBA: intersect(Target, Union(cel1, cel2, cel3))

because you are a novice: be aware of this fact

the code must be written in the needed Sheet's code (not in a module,
neither in the workbook's code) because the events' subs will be called
only with this sheet...

Thank you so much for your patience - I think I understand now. However,
what I can not see is this - the user simply has the worksheet open - it
consists of some columns of data cells, any of which may need editing (but
not deleting, cutting etc.), and hidden between those columns are two
columns of formulas that refer to the data cells. The formulas are like
this:-

=IF(OR(B5="",B5="-"),"-",IF(RIGHT(B5,2)="OR","-",LOOKUP(B5,Crib!$A$1:$A$272,Crib!$B$1:$B$272) ))

- the formula examines the data cell, and gives a numeric result that it
looks up in a VLOOKUP sheet if the value in the data cell matches one of the
column A values in the VLOOKUP (Crib). The problem I've been having is that
the user sometimes cuts and pastes a cell into the location s/he is editing,
instead of editing the cell's contents. This gives a #REF! error as the
result of the formula, because the cell (B5 in this case) is no longer
there. Now, what I can't see is how your macro method can be applied to
this situation - does the user have to run a macro in order to edit the
cell, or how does it work practically? Sorry to be dumb!

TIA,

Dan
"D.2" wrote in message
...

because you are a novice: be aware of this fact

the code must be written in the needed Sheet's code (not in a module,
neither in the workbook's code) because the events' subs will be called
only with this sheet...

At this point maybe you should
1- read what was said (the user needs to double-click on the locked
cell he wants to edit)

2- try to make it (because you need your "first time")



Just lock the cells you want to protect, protect the sheet, and type in
the macro in the VBA editor (the code must be associated with the right
sheet)


If you realy don't know how the VBA editor works, maybe you should
read and try with examples (take a look on the web for tutorials) or ask
a friend... But at this point you should stop asking and start trying...

this "learning time" is required.

OK, thanks, D.2 - your help and advice is much appreciated.

Thanks again,

Dan
"D.2" wrote in message
...

At this point maybe you should
1- read what was said (the user needs to double-click on the locked cell
he wants to edit)

2- try to make it (because you need your "first time")



Just lock the cells you want to protect, protect the sheet, and type in
the macro in the VBA editor (the code must be associated with the right
sheet)


If you realy don't know how the VBA editor works, maybe you should read
and try with examples (take a look on the web for tutorials) or ask a
friend... But at this point you should stop asking and start trying...

this "learning time" is required.