Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Red face Unanswered: Protected Sheet + Sorting Autofilter = Headache

    Hi all,

    Done my research on this, and all I can find is people saying: "Use a Macro to unprotect the sheet, do your sort, then reprotect the sheet".

    That's all very well and good, but how can I add this functionality to when someone is using the 'Sort A to Z', 'Sort Oldest to Newest', etc, buttons in the Autofilter menu?

    I assume an event is triggered that I could dump my unprotect/protect code into, but I can't find it anywhere.


    AFAIK, using a macro will only work if I want someone to be able to sort, say, column C from oldest to newest, and make a button for it.

    I want the user to have the same functionality, sort wise, as when the sheet is unprotected... So maybe:

    Sort by Date of Sales - Then - Sort by Surname - Then - Sort by First Name

    On the fly, for whatever the user wants to see.


    Any advice/examples..?

    Thanks guys.
    Looking for the perfect beer...

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    This one's not easy. There isn't an built-in event you can trap to pre-empt a user sorting via the autofilter. Other than unprotecting the sheet, an option might be to give your users some functionality so that they can double click on the header row of the autofilter and it will sort the data by that column.

    So a basic starting point would be this code in the worksheet's class module:

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      
        '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
      
                'sort ascending  by that column
                Me.AutoFilter.Range.Sort _
                            Key1:=Target, _
                            Order1:=xlAscending,  _
                            Header:=xlYes
     
            End If
        End If
     
        'cancel the double click otherwise user will get an error if the cell is protected
        Cancel = True
     
    End Sub
    So, this is very simple. It checks if there's an autofilter, then it checks if the user double clicked on the header row of the autofilter's range, and then it sorts the autofilter range by that column. When the worksheet is protected, sorting must be allowed.

    If you like the approach then you could build it out. For example, so that it toggles between sorting ascending and descending each time the user double clicks on the cell. To do that, you could use a dictionary object to remember how many times each header cell has been clicked:
    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        
        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
        
        'cancel the double click otherwise user will get an error if the cell is protected
        Cancel = True
        
    End Sub
    And so on...
    Last edited by Colin Legg; 03-01-12 at 14:34.

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Thumbs up

    This is very interesting, thanks for your quick and thorough response Colin.

    I can't for the life of me why after the revamped 2007, and then 2010 office, Microsoft still don't see fit to allow this to be achieved simply. I can't imagine that using an autofilter to view statistical information, and wanting to sort it, whilst maintaining the integrity of the data is that rare of a request.

    Either way, your solution - although not ideal (through no fault of your own), is suitable, and should work a treat. Thanks a lot.

    I'm also unfamiliar with this Dictionary object. From what I can see in your code it looks to be similar to a Map (TreeMap / HashMap) in Java? am I to assume its a collection that takes key-value pairs? The key being a distinct/unique object/value?
    Looking for the perfect beer...

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    I can't for the life of me why after the revamped 2007, and then 2010 office, Microsoft still don't see fit to allow this to be achieved simply.
    I agree with you. If filtering and sorting are enabled when the worksheet is protected, then one should be able to sort on a filtered list. Others may disagree, but I view this as a bug. Let's hope it's fixed in the next release...
    I'm also unfamiliar with this Dictionary object. From what I can see in your code it looks to be similar to a Map (TreeMap / HashMap) in Java? am I to assume its a collection that takes key-value pairs? The key being a distinct/unique object/value?
    Yes. Within the VBA world, it's comparable to a VBA Collection, but with a few differences and some added functionality. It's part of the MS Scripting Runtime library (scrrun.dll) so if you want to early bind you'll need to go into tools > references and tick "Microsoft Scripting Runtime", and then the variable declaration would be Static dicCells As Scripting.Dictionary. I used it here rather than an array or a collection because of it's Exists method.

Posting Permissions

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