Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Idiot proofing my spreadsheet
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 specialvalues). 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Idiot proofing my spreadsheet
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 "G Love" groups.com... 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 specialvalues). 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Idiot proofing my spreadsheet
Take a look at the OFFSET function. Use this in your formulas to refer to
user entered cells rather than the actual cell address. "G Love" wrote in message ups.com... 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 specialvalues). 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to make a spreadsheet idiot-proof | Excel Worksheet Functions | |||
I want to checkoff items as I'm proofing them | Excel Discussion (Misc queries) | |||
Tamper proofing | Excel Discussion (Misc queries) | |||
User proofing an entry | Excel Programming | |||
Error proofing automatic updating a cell | Excel Programming |