Results 1 to 4 of 4

Thread: Locking a certain number of Cells in an Excel Sheet

  1. #1

    Post Locking a certain number of Cells in an Excel Sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello Guys,

    Is there a way to lock a certain number of fields in Excel sheet. ie. no one is able to access amend them except the administrator who has the password for it.
    Although I have just done a work around by putting a data validation on the fields .

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Do you have an Active Directory domain?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Conjurer Roger Govier's Avatar
    Join Date
    Mar 2011
    Location
    Located near Abergavenny, South Wales, UK
    Posts
    113
    Articles
    0
    Hi
    By default all cells in a sheet are Locked, so as soon as you Protect a sheet, you are unable to change them.
    So, select all of the cells other than those you want to be locked, and then choose Format Cells>Protection and remove the check mark.
    Now, protect your sheet with a Password and those cells you have chosen will be locked from user amendment.
    Regards

    Roger Govier
    Microsoft Excel MVP
    http://www.technology4u.co.uk

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,250
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hiya Roger,

    The question was a little ambiguous, but I was thinking something bigger that dealt with specific users:

    Starting with Excel 2002, if you have a domain at work, you can lock cells and give permissions to certain individuals to be able to edit them. To do this:
    • Excel 2002/2003 Tools-->Protection-->Allow Users To Edit Ranges
    • Excel 2007+ Review-->ChangesàAllow Users To Edit Ranges
    The dialog box that results is shown below. Its concept is that you need to specify the ranges that users CAN edit. To do so you’d click New.

    Click image for larger version. 

Name:	protect1.jpg 
Views:	5 
Size:	15.4 KB 
ID:	529

    Once done, you’ll see the dialog box shown below:

    Click image for larger version. 

Name:	protect2.png 
Views:	4 
Size:	13.9 KB 
ID:	530

    Give the range a title and a range then also provide a password. This is a very important step, as if you do not provide a password then this setup will have no effect.

    Finally, click Permissions and add the users you wish to include. And don’t forget to include yourself or you may also be locked out (which can, of course, be undone by unprotecting the workbook).

    While we have tested this interesting feature in our offices, I admit it is not one we use, as it seems like it would be difficult to maintain. Regardless, it could offer value in some situations.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training) or with my book M is for Data Monkey!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

Posting Permissions

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