locked
Sort function of AutoFilter won't work on protected sheet RRS feed

  • Question

  • I need to give my users the ability to sort column A:I so I turn on the AutoFilter which contains sort functions in the drop down menu.  I then protect the sheet and set the Sort and AutoFilter checkboxes to TRUE.  But when I click on Sort Ascending I get a msgbox saying I can't sort a protected sheet, why?

    Ryan

    Tuesday, May 21, 2013 12:54 PM

Answers

  • It's a consequence of the concepts of (un)locking and protecting: sorting a range changes cells, so in order to sort a range, the user must be able to change it.

    You could keep the cells locked, and create a command button that runs a macro that:

    • Unprotects the sheet.
    • Sorts the range.
    • Protects the sheet.

    Regards, Hans Vogelaar

    • Marked as answer by Ryan0827 Tuesday, May 21, 2013 8:47 PM
    Tuesday, May 21, 2013 7:25 PM

All replies

  • The entire range that you want to allow the user to sort, should be unlocked. If the range contains any locked cells, you won't be able to sort it when the sheet is protected, even though you allowed sorting.

    Regards, Hans Vogelaar

    Tuesday, May 21, 2013 2:15 PM
  • If I unlock the cells in the range I want to sort, then protect the sheet, the user will be able to change the data. That defeats the whole purpose of protecting the sheet, ugh. Why then does Microsoft give you the option to Sort or AutoFilter a protected sheet then not allow you to do it? Am I missing something here or is Microsoft gone retarded again? Do you have any suggestions on how to protect my data and still allow sorting? I've tried a DataTable with same result. Ryan

    Ryan

    Tuesday, May 21, 2013 5:51 PM
  • It's a consequence of the concepts of (un)locking and protecting: sorting a range changes cells, so in order to sort a range, the user must be able to change it.

    You could keep the cells locked, and create a command button that runs a macro that:

    • Unprotects the sheet.
    • Sorts the range.
    • Protects the sheet.

    Regards, Hans Vogelaar

    • Marked as answer by Ryan0827 Tuesday, May 21, 2013 8:47 PM
    Tuesday, May 21, 2013 7:25 PM
  • I figured I'd have to write some macros to do the sorting.  I just think it's silly that Microsoft puts a checkbox for AllowSorting and AllowFiltering in the Worksheet.Protect method dialog box and then doesn't let you sort or filter.

    I mean, whats the point of protecting a sheet and then leaving all the cells unlocked that contain data?

    I figure Microsoft could have wrote some code to do what you are proposing and what I have to write now.  Booooo.

    Best Regards,


    Ryan

    Tuesday, May 21, 2013 8:47 PM
  • Agreed. It is a long standing (Microsoft retarded related) problem that someone is too lazy to fix. 
    • Proposed as answer by cwra007 Saturday, April 5, 2014 10:00 AM
    • Unproposed as answer by cwra007 Saturday, April 5, 2014 10:00 AM
    Saturday, April 5, 2014 9:53 AM
  • Here is a work around that sorts ascending to descending (vice versa) on a double click on the header cell of a filtered range.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        
        'Unprotect the sheet
        ActiveSheet.Unprotect
        
        Static dicCells As Object
        
        'does the sheet have an autofilter?
        If Me.AutoFilterMode Then
            
            'did the user double click on the header row of the autofilter?
            If Not Intersect(Me.AutoFilter.Range.Rows(1), Target) Is Nothing Then
                
                If dicCells Is Nothing Then Set dicCells = CreateObject("Scripting.Dictionary")
                
                If dicCells.Exists(Target.Address) Then
                    dicCells.Item(Target.Address) = dicCells.Item(Target.Address) + 1
                Else
                    dicCells.Add Key:=Target.Address, Item:=1
                End If
                
                'sort ascending/descending by that column
                Me.AutoFilter.Range.Sort _
                            Key1:=Target, _
                            Order1:=IIf(1 And dicCells.Item(Target.Address), xlAscending, xlDescending), _
                            Header:=xlYes
            
            End If
        End If
        
        'Reprotect the sheet
            ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , AllowFormattingCells:=True, AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True
        
    End Sub

    • Proposed as answer by cwra007 Saturday, April 5, 2014 10:03 AM
    Saturday, April 5, 2014 10:03 AM