Deleting info in locked cells/columns

[txnc]

I have locked all the cells in column B as it is calculating a formula. Columns A and C contain unlocked data that feeds into column B. If you select cells in column A and drag it across to C and hit "delete," it deletes the information and therefore the formula in column B, despite the fact that it is locked.

Any thoughts on how to prevent this from happening? I am trying to make this spreadsheet as 'idiot-proof' as possible, and just know someone is going to delete the formula on accident...any thoughts?

Thanks in advance!

[ragulduy]

Locking the cell on it's own will not prevent it being "deleted", you also need to protect the worksheet.

[davesexcel]

You do not mention if you have protected the worksheet or not. Locking and unlocking cells have no effect unless you protect the worksheet.

[TMS]

Locking the cells does nothing unless the worksheet is protected with a password (although the password could be blank, for convenience). is the sheet protected?

And maybe it would be better if you changed the order of the columns so the formula is not between two cells that you want to clear.

Regards, TMS

[txnc]

Thank you all for the replies - I should have specified, I have protected the worksheet as well. The columns are part of a table that, for ease of information's sake, should probably not be rearranged.

I appreciate the input!

[popipipo]

Can you post the xls file

[newdoverman]

If you properly Unlock the cells that you want access to (locked is the default) and when protecting the worksheet select only Select Unlocked Cells and then Protect the worksheet you then can only access the unlocked cells. If you click on an unlocked cell and drag it across to another unlocked cell either in the same column or in another column, the result will be a #REF error in the locked cells. It isn't that the formula isn't there it is that the references in the formula have been messed up.

[txnc]

Willem & Ron,

Here is an abbreviated version of the sheet I am working with. As you can see, Columns C, D, F, and G need to be able to be edited. It is information in the E column ("Miles") that I have locked that people continue to delete.

I know I could move that column over and eliminate the problem, but it was requested I leave the grouping as is.

I know this is a very minor thing...I am just curious as to whether or not there is a solution!

Thanks again for all your help!

[ragulduy]

You have an "alloweditranges" assigned to the sheet which I think will supercede the locked/unlocked property. I'm not sure how to remove it without vba but if you press alt+f11 to open the vba editor, click insert-> module and then copy this code:

Please Login or Register  to view this content.

into it and run the macro (place the cursor on the sheets("... line and press F5).

Then when you protect the sheet column E should be protected

[newdoverman]

I think that it might be possible using VBA to only select a cell and enter data but not move, copy or delete it. I can see your problem and was able to duplicate it many times but I couldn't prevent it. I could do is using either the left mouse button or the right button...didn't matter.

A user form might be the answer where the data can be entered and sent to a data sheet but direct access to that data sheet be blocked.

A VBA expert would perhaps be able to offer a good solution.

[txnc]

Got it. Thank you so much for taking a look at it, I really appreciate it!

[txnc]

Thank you so much for your help, this is great!

[newdoverman]

How and where did you find this?

[txnc]

This forum or this spreadsheet?

The spreadsheet is a mileage calculation form. My supervisor created the table skeleton and then asked me to fill it in with the formulas and formatting.

[newdoverman]

I should have said @ yudlugar how did you find the alloweditranges and where is it?

[popipipo]

Is this what your looking for?
Copy , paste move is disabbled with this vba.
Be sure to enable it if you leave this sheet.
If not, other files that you open are also forbidden to copy, paste, move

[ragulduy]

@ Newdoverman,

I went into the vba editor and added a watch for the sheet, I then looked through the sheet properties, particular those in the .protection category and found that there was an item in the .alloweditranges collection.

[newdoverman]

@ yudlugar, Thank you for the explanation. I must get a book on VBA and try my hand at it.

[TMS]

You don't need VBA.

If you unprotect the worksheet, you can click on the Review Tab and, on there, at the right hand side, click on Allow Users to Edit Ranges.

That will display a dialogue box identifying the ranges and permissions. In this case, Mileage Table 2 refers to =$L$2:$L$32,$C$2:$H$32. In that dialogue box, you have the options to modify or delete the range.


Regards, TMS