+ Reply to Thread
Results 1 to 4 of 4

Unable to get the sort feature to work from the header on a protected worbook (Excel 2010)

  1. #1
    Registered User
    Join Date
    06-20-2014
    Location
    Seattle
    MS-Off Ver
    MS Excel 2010
    Posts
    2

    Unable to get the sort feature to work from the header on a protected worbook (Excel 2010)

    I have an Excel 2010 file that we will be sending out to 50 users and we need them to be able to enter data in some cells and not in others within the same table. That alone works fine by protecting the sheet. The catch is that we also need them to be able to sort and filter. We did the allow users to edit range trick and checked the “Auto Filter” and “sort” when protecting and it does work, but it only allows the user to sort by right clicking and sorting the used range and not simply by using the drop down and selecting sort A-Z (see screen shots below) and sorting the entire table. It’s fine if that’s just the way it has to be, but it’s not very user-friendly. I’ve attached a sample of the file (with fake data) so you can see what I mean. The blue cells are ones we want people to be able to edit and the other cells will have data we supply in them. The sheet is protected and there is no password (just hit enter).
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Unable to get the sort feature to work from the header on a protected worbook (Excel 2

    I'm unable to open your attachment. I don't know if the problem is yours, mine, or the web site's. You might try re-attaching.
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

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

    Re: Unable to get the sort feature to work from the header on a protected worbook (Excel 2

    The cells that you wanted to allow access to were "locked" and this prevented any entry or sorting of the data.

    Select the cells that you want to allow access to, right click and select Format Cells, Cell Protection, deselect Locked.

    I took a guess at what you want protected and not protected.....no password used.
    Attached Files Attached Files
    <---------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

  4. #4
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Unable to get the sort feature to work from the header on a protected worbook (Excel 2

    The OP is describing the difference in the way Excel behaves depending on whether you select the sort option from right-clicking an unlocked cell in a particular column (this works) vs selecting the sort option from right-clicking on the drop down header of the column itself (doesn't work).

    When the sheet is protected but allows sorting, the sort option from the column header does not behave.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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. Replies: 5
    Last Post: 06-20-2014, 02:12 PM
  2. Can a macro work in a protected [Excel 2010] sheet?
    By earljohnson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2014, 05:00 PM
  3. Unable to sort with protected sheet
    By milt2010 in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 12-30-2013, 02:45 PM
  4. Drag feature in Excel 2010 not working
    By Maheshgx in forum Excel General
    Replies: 4
    Last Post: 08-13-2012, 09:02 AM
  5. Replies: 5
    Last Post: 07-31-2012, 11:58 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