+ Reply to Thread
Results 1 to 11 of 11

Disable sorting but allow filtering

  1. #1
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Disable sorting but allow filtering

    Hello everyone

    I want to prevent sorting of columns in a single worksheet but allow filtering. I came across this macro for Excel 2003 in another forum but it doesn't seem to work for me (I'm using 2010). http://www.ozgrid.com/forum/showthread.php?t=49308

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I entered it in the view code bit of the worksheet I want it applied to which I'm guessing is the right way to go about it?

    Thanks
    Last edited by EdwardStephenson; 04-23-2014 at 05:48 AM.

  2. #2
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Disable sorting but allow filtering

    Anyone help on this?

  3. #3
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Disable sorting but allow filtering

    There must be some guru who knows if this is possible or not?? Please help!

  4. #4
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Disable sorting but allow filtering

    Don't enter it in a module or a normal subroutine (sub).

    Go into your VBA editor (Ctrl+F11), Then click 'this workbook, (if you want it applied to whole workbook). In the drop down boxes above the code area, find 'WORKBOOK' and 'OPEN'. Drop the two lines of code in there. If the code is correct (assuming it is!) this will trigger every time the workbook is opened. You will need to save and re-open for it to kick in.

    If this doesn't work, I will look at the body of the code for you.

  5. #5
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Disable sorting but allow filtering

    Hi TKCZBW

    Thanks so much for the reply. Ctrl+F11 adds a new worksheet named Macro1 - thats not right is it? Before I was rightclicking on the sheet1 tab at the bottom, then clicking view code, then double clicking on sheet1 and entering the code in there, as that's the only sheet I want it applied to. I just tried entering the two lines of code only and it still won't work...

  6. #6
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Disable sorting but allow filtering

    No, you don't want a macro worksheetsheet (they aren't really supported any more and so really only good for backwards compatability).

    Go into your worksheet code, as before, then select 'WORKSHEET' and 'ACTIVATE'. drop the code in there.

    When you say it doesn't work, do you mean it errors out, or it just doesn't do what you want?

  7. #7
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Disable sorting but allow filtering

    Okay I see, apologies I was being stupid - the macro is now running, but it doesn't do what I want it to. I want 100% functionality with everything apart from sorting. I want to be able to filter but just no sorting. This macro seems to just protect the worksheet which isn't what I want at all. I know I can protect a worksheet and disable sorting only but that also disables any kind of data entry which is something I don't want.

    Thanks

  8. #8
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Disable sorting but allow filtering

    OK. I would actually protect the sheet, but then on the protect sheet dialogue allow users to actually do everything but sort the cells. I've just tryed it out and it seems to do what you want.

    You need to enable filtering before you protect the sheet (i.e. 'CTRL+L') otherwise you won't even be able to filter.

  9. #9
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Disable sorting but allow filtering

    I want to be able to edit cells though and protecting sheets stops this

  10. #10
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Disable sorting but allow filtering

    Nope, not if you go to the protectsheet dialogue box (which version of excel are you in?) you can set it to allow editing of cells and just lock sorting. You will also need to select the entire sheet, go to format cells and turn off 'locked'. This should do it.

  11. #11
    Registered User
    Join Date
    04-07-2014
    Location
    Berkshire, England
    MS-Off Ver
    Excel 2010
    Posts
    52

    Re: Disable sorting but allow filtering

    It works!! Thanks!

  12. #12
    Forum Contributor
    Join Date
    02-28-2012
    Location
    London, England
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    187

    Re: Disable sorting but allow filtering

    Good!

    Best,

    TKCZBW

+ 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. Excel 2007 : disable sorting in pivot table
    By somedude in forum Excel General
    Replies: 6
    Last Post: 10-30-2011, 07:42 PM
  2. sorting and filtering
    By Debra Dalgleish in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-06-2005, 07:05 AM
  3. sorting and filtering
    By Duncan McDowell in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  4. Filtering and Sorting
    By pmguerra in forum Excel General
    Replies: 0
    Last Post: 03-17-2005, 12:31 PM
  5. Sorting and filtering
    By dsbiloxi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-10-2005, 08:06 PM

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