+ Reply to Thread
Results 1 to 19 of 19

Deleting info in locked cells/columns

  1. #1
    Registered User
    Join Date
    01-06-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Deleting info in locked cells/columns

    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!

  2. #2
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Deleting info in locked cells/columns

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

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    12,504

    Re: Deleting info in locked cells/columns

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

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,785

    Re: Deleting info in locked cells/columns

    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
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    01-06-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Deleting info in locked cells/columns

    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!

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,867

    Re: Deleting info in locked cells/columns

    Can you post the xls file
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Deleting info in locked cells/columns

    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.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  8. #8
    Registered User
    Join Date
    01-06-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Deleting info in locked cells/columns

    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!
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Deleting info in locked cells/columns

    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

  10. #10
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Deleting info in locked cells/columns

    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.

  11. #11
    Registered User
    Join Date
    01-06-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Deleting info in locked cells/columns

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

  12. #12
    Registered User
    Join Date
    01-06-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Deleting info in locked cells/columns

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

  13. #13
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Deleting info in locked cells/columns

    How and where did you find this?

  14. #14
    Registered User
    Join Date
    01-06-2014
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Deleting info in locked cells/columns

    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.

  15. #15
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Deleting info in locked cells/columns

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

  16. #16
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    2,867

    Re: Deleting info in locked cells/columns

    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
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Deleting info in locked cells/columns

    @ 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.

  18. #18
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Deleting info in locked cells/columns

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

  19. #19
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    30,785

    Re: Deleting info in locked cells/columns

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Locked worksheet based upon date and locked columns when worksheet is not locked
    By ruralbrew in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-09-2011, 05:18 PM
  2. [SOLVED] Deleting and shifting cells and columns
    By Curt1521 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-08-2006, 11:20 AM
  3. Deleting info from all cells at once
    By days_crazed in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2006, 02:40 PM
  4. Put comments on a locked spreadsheet even though cells not locked
    By RDP in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2005, 07:05 PM
  5. Deleting rows with locked cells
    By Paul Moore in forum Excel General
    Replies: 3
    Last Post: 06-18-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1