Prevent changing value in cell

I want to allow users to select a cell in a column, but not allow them to change the contents of the cell.
Also, don't want to allow them to select all the cells on the sheet.

I'm guessing that I need to use the Change event, and restore the contents back to what they were before the change.

Is there a better way to do this?

Hi Foxguy,

Is worksheet protection not an option? That's kind of the entire point behind worksheet protection - allow users to edit what you want them to edit, and lock what they shouldn't. You can even set it so that they can't select locked cells when you're adding the worksheet protection.

Right-click on the cells you want users to edit, choose Format Cells. On the Protection tab un-check 'Locked'. Protect the worksheet, and near the top of the list of allowable options, un-check 'Select Locked cells'.

That should do it.

In which case I suspect you may need to use a Worksheet_Change event, ie:

a) unprotect all cells that can be selected
b) protect sheet and set such that protected cells can NOT be selected
c) install the below into the Sheet Object on which the cells reside (revising range as required):

Please Login or Register  to view this content.

In the above cells B4, B7 & B9 can all be selected but if they are altered the action is reversed and user notified as such.

Obviously this then requires users have enabled macros in the first instance etc...

Why not test it and see...

No.... I can't think of a good way to put it but the help file is essentially making the point that you should run the Undo call prior to performing any other "recordable" actions... in the code provided no such actions are performed prior to the Undo being called.

No, I can't see MS removing the Undo feature, can you ?

Note: this isn't undocumented it's a case of interpretating the narrative in the help file, as I say the key is to call the Undo before any further "recordable" actions are undertaken else the wrong action will be undone...

The reason we disable Application Events prior to calling the Undo action is that if we didn't, given we're in a Change event, we'd end up in a perpetual loop... the Undo would obviously change the sheet which would in turn invoke the change event which would in turn invoke the Undo which would in turn invoke the change event which would in turn...

In which case I suspect you may need to use a Worksheet_Change event, ie:

a) unprotect all cells that can be selected
b) protect sheet and set such that protected cells can NOT be selected
c) install the below into the Sheet Object on which the cells reside (revising range as required):

Please Login or Register  to view this content.

In the above cells B4, B7 & B9 can all be selected but if they are altered the action is reversed and user notified as such.

Obviously this then requires users have enabled macros in the first instance etc...

This is very nice!