Idiot proofing my spreadsheet

[G Love]

Hi there,

I've spent a long time creating a spreadsheet with numerous conditional
formats and complex formulae, I have locked the cells that must never
be changed and some of these react to data input by my colleagues into
other related cells. However, when they put a value in the wrong cell
they are in the understandable habit of copying and pasting the info to
the place it should be and this has the nasty result of screwing up the
cell references and thus returns errors in the locked cells.

What i need to do is prevent them from pasting over the cell formats
while still being able to copy and paste info (i.e. only allow paste
special>values).

I can't use a macro for restricting ctrl+v to paste special as most of
them don't use shortcuts they use mouse-clicks and visual buttons to do
their cutting copying and pasting.

Ideally what i would want is a sheet where all cells are locked but
have individual characteristics that allow different actions. e.g.;

A1 Totally locked, can't be selected, edited or modified
B1 Locked, can be selected and text entered but not formatted
C1 Locked, can be selected but will only accept input from
drop-down menu

Please tell me this is possible as it is frustrating to see so much
hard work ruined by a few inept clicks of a mouse, and if at all
possible I'd rather avoid macros.

Thanks

Mike

 

[NickHK]

Mike,
You can maybe combine this code, with a check on the range affected.

NickHK

Dim Cutting As Boolean
Dim PossibleD_DOp As Boolean
Dim D_DOccured As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Msg As String
'Exit Sub
If PossibleD_DOp = True Then
D_DOccured = True
Else
Select Case Application.CutCopyMode
Case False
If Cutting Then
MsgBox "Was Cut"
Else
MsgBox "Normal Entry"
End If
Case xlCopy
MsgBox "Pasted Copy"
End Select
End If
PossibleD_DOp = True

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cutting = (Application.CutCopyMode = xlCut)
If D_DOccured = True Then
MsgBox "Drag-Drop Operation"
D_DOccured = False
End If
PossibleD_DOp = False
End Sub

 

[M. Authement]

Take a look at the OFFSET function. Use this in your formulas to refer to
user entered cells rather than the actual cell address.