VBA Allow Sorting on Protected Sheet

[avd88]

Hello,

I'm trying to allow users to both filter and sort protected sheets with VBA.
The code I currently have is working for the filtering part of it but the sorting does not.

This is my full code for that section, the protect part of it is on the bottom:

Private Sub SubmitButton_Click()

    'Sheets("IDN").Activate THIS LINE MESSES UP THE VALUE FOR THE ACCOUNT LABEL IN THE USERFORM DON'T USE
    
    Sheets("RDs").Cells(1, 4) = Me.RdComboBox.Value
    Sheets("RDs").Cells(2, 4) = ""
    
    'FILTER ALL TABLES'
    Dim IDNLastRow As Long
    Dim BTLastRow As Long
    Dim NPILastRow As Long
    Dim ENDLastRow As Long
    
    Dim RDName As String
    RDName = Me.RdComboBox.Value
    
    'FIND LASTROW AND FILTER
        IDNLastRow = Sheets("IDN").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("IDN").Range("A2" & ":S" & IDNLastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName
                                
        BTLastRow = Sheets("BT").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("BT").Range("A2" & ":T" & BTLastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName

        NPILastRow = Sheets("NPI").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("NPI").Range("A2" & ":T" & NPILastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName

        ENDLastRow = Sheets("END").Cells(Rows.Count, "A").End(xlUp).Row
        Sheets("END").Range("A2" & ":T" & ENDLastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName
                                
        'Protect Sheets
        Sheets("IDN").Protect , AllowFiltering:=True, AllowSorting:=True
        Sheets("BT").Protect , AllowFiltering:=True, AllowSorting:=True
        Sheets("NPI").Protect , AllowFiltering:=True, AllowSorting:=True
        Sheets("END").Protect , AllowFiltering:=True, AllowSorting:=True
    
    Unload Me
End Sub

 

[rlv01]

Filtering does not change the value of a cell, but sorting does. You cannot sort on a protected sheet unless you uncheck the "Locked" property of the cells to be sorted.

 

[avd88]

Thanks for the reply, that makes sense. I believe I can have the table sorted first and just leave the filtering option on.
I'm getting an error when trying to sort though. :

"The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank."

The table with headers starts in cell A2 and I'm trying to sort by column B.

This is my code:

Private Sub SubmitButton_Click()

    'Sheets("IDN").Activate THIS LINE MESSES UP THE VALUE FOR THE ACCOUNT LABEL IN THE USERFORM DON'T USE
    
    Sheets("RDs").Cells(1, 4) = Me.RdComboBox.Value
    Sheets("RDs").Cells(2, 4) = ""
    
    'FILTER ALL TABLES'
    Dim IDNLastRow As Long
    Dim BTLastRow As Long
    Dim NPILastRow As Long
    Dim ENDLastRow As Long
    
    Dim RDName As String
    RDName = Me.RdComboBox.Value
    
    'FIND LASTROW, FILTER AND SORT
    
    'LAST ROWS
    IDNLastRow = Sheets("IDN").Cells(Rows.Count, "A").End(xlUp).Row
    BTLastRow = Sheets("BT").Cells(Rows.Count, "A").End(xlUp).Row
    NPILastRow = Sheets("NPI").Cells(Rows.Count, "A").End(xlUp).Row
    ENDLastRow = Sheets("END").Cells(Rows.Count, "A").End(xlUp).Row
    
    
    'SORT
        Sheets("IDN").Range("A2" & ":T" & IDNLastRow).Sort key1:=Range("B2" & ":B" & IDNLastRow), _
        order1:=xlAscending, Header:=xlYes
                            
    'FILTER
        Sheets("IDN").Range("A2" & ":T" & IDNLastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName
    'SORT
        Sheets("BT").Range("A2" & ":U" & BTLastRow).Sort key1:=Range("B2" & ":B" & BTLastRow), _
        order1:=xlAscending, Header:=xlYes

    'FILTER
        Sheets("BT").Range("A2" & ":U" & BTLastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName
                                
                                
        Sheets("NPI").Range("A2" & ":U" & NPILastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName

        Sheets("END").Range("A2" & ":U" & ENDLastRow).AutoFilter Field:=1, _
                                 Criteria1:=RDName
                                
        'Protect Sheets
        Sheets("IDN").Protect , AllowFiltering:=True
        Sheets("BT").Protect , AllowFiltering:=True
        Sheets("NPI").Protect , AllowFiltering:=True
        Sheets("END").Protect , AllowFiltering:=True
    
    Unload Me
End Sub

 

[rlv01]

Hard to know based on the info, but I suspect that you need to fully qualify the range you are using for key1. Try this:

   With Sheets("IDN").Range("A2" & ":T" & IDNLastRow)
        .Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes
    End With

    With Sheets("BT").Range("A2" & ":U" & BTLastRow)
        .Sort Key1:=.Range("B2"), Order1:=xlAscending, Header:=xlYes
    End With

 

[avd88]

Thanks a bunch!! It seems it was missing the With / End With statement.

 

[Hampo]

I have a spreadsheet with several columns that I want to protect but also sort & filter. I can set this up OK in excel and not have to use VBA. However on top of sorting & fiitering I want to execute any number of hyperlibnks that are in the cells that have been sorted without compromising the sheet protection. I wonder if there is a VBA solution but I cannot find it. Help please!