• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Worksheet Protection and Tables

P

PaulFogel123

Member
I have a sheet with several tables, all sortable in the usual way. I would like to protect the sheet, but filtering is then rendered inactive. I've tried various options in the Protect Worksheet form, but any sort of protection disables filtering in tables.

Does anyone have a solution? I think this will involve a little code.

Thanks!
 
That option only seems to apply to regular filtered ranges, not tables. Selecting Use Autofilter allows the display of all the sort options--but it doesn't work. Try sorting smallest to largest, for example, and nothing happens.
 
Sorting changes data so protection doesn't make sense then.

If you want to allow a sort option, you need to do it in a macro.
 
Hi ,

See the attached file , which has a table in a worksheet that has been protected.

Are you able to use all the features of sorting and filtering ?

Narayan
 

Attachments

  • Book 1.xlsx
    9.3 KB · Views: 5
Narayan, if the cells are not locked, then protection means nothing. So, yes, all of your data can be sorted in that filter.

I guess if you want the user to be able to change the table data then unlock the cells accordingly or use a macro as I explained.
 
It has been protected, but the cells haven't been. When cell protection is enabled (Protection/Locked/Hidden) then filtering doesn't work.
 
Actually, you can set the worksheet's Protect with the UserInterfaceOnly:=True to let code make changes without Unprotect. I normally put that in ThisWorkbook's Open event. I would use the "Use AutoFilter" Protect option as well.
 
Kenneth, if this works, you're a genius. It would be easier for me, being only moderately skilled in VBA, if you'd create a little table file with the code. Then I could see what you'd done and adapt it for use. I have several places in my workbook with tables. I'm most concerned with users inserting columns or rows or overwriting formulas, so this fix would be great.
 
It would be easier if you posted a small sample file. I recently helped a user that used Form Checkbox and also a Form ListBox control to AutoFilter by a columns set of values.

You might want to add maybe Form or ActiveX Checkbox controls for one or both sort options. If you put them in the row above the table, I will know which Field/Column that you want sorted. Option Button controls could be another way to go as well. Insert those controls from the Developer Tab. Edit the ribbon to add it if needed.

The code is not that difficult.
 
Kenneth Hobson said:
It would be easier if you posted a small sample file. I recently helped a user that used Form Checkbox and also a Form ListBox control to AutoFilter by a columns set of values.

You might want to add maybe Form or ActiveX Checkbox controls for one or both sort options. If you put them in the row above the table, I will know which Field/Column that you want sorted. Option Button controls could be another way to go as well. Insert those controls from the Developer Tab. Edit the ribbon to add it if needed.

The code is not that difficult.
Click to expand...
I am posting the actual sheet with several tables in it. All the columns are sortable. I pasted values over the formulas to eliminate links. I'm using binary format to cut down on file size and it's fully compatible with VBA.
 

Attachments

  • Svc Line Analysis.xlsb
    740.7 KB · Views: 3
Kenneth Hobson said:
Narayan, if the cells are not locked, then protection means nothing. So, yes, all of your data can be sorted in that filter.

I guess if you want the user to be able to change the table data then unlock the cells accordingly or use a macro as I explained.
Click to expand...
Hi ,

The tables themselves are not locked , if that is what you mean.

If the tables are locked , then sorting or filtering will not work , and code will be required to unprotect the tables / worksheet itself , and do the sorting / filtering using VBA.

The original post did not specifically mention that the tables themselves were locked. Only that the worksheet was locked.

Narayan
 
Looks like nice work. What columns or exact cells did you want to sort and sort ascending or descending?

This code shows C5 sorting both ways. I did not know which you wanted or how you wanted to run it. Mainly, one records a macro and then sets the sheet's Protect options as explained earlier to get started.

The code can be cleaned up a bit but with ListObjects(), table, code is a bit more involved. Luckily, the macro recorder makes doing it fairly easy.
Code: 
Sub Macro2()
'c5 sorts...
    ActiveSheet.Unprotect
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
 
I'd like all the columns sortable, both ascending or descending.
NARAYANK991 said:
Hi ,

The tables themselves are not locked , if that is what you mean.

If the tables are locked , then sorting or filtering will not work , and code will be required to unprotect the tables / worksheet itself , and do the sorting / filtering using VBA.

The original post did not specifically mention that the tables themselves were locked. Only that the worksheet was locked.

Narayan
Click to expand...
My intent is to protect the worksheet and its formulas from being altered. All the table cells would have locked/hidden checked.
 
Kenneth Hobson said:
Looks like nice work. What columns or exact cells did you want to sort and sort ascending or descending?

This code shows C5 sorting both ways. I did not know which you wanted or how you wanted to run it. Mainly, one records a macro and then sets the sheet's Protect options as explained earlier to get started.

The code can be cleaned up a bit but with ListObjects(), table, code is a bit more involved. Luckily, the macro recorder makes doing it fairly easy.
Code: 
Sub Macro2()
'c5 sorts...
    ActiveSheet.Unprotect
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Click to expand...
Forgive my ignorance, but how would I attach this code to the sort arrows in the table headers. Can that be done?
 
The reason I used tables was that you can have multiple, sortable tables on the same sheet. A normal range with Autofilter allows only one to a sheet.
It seems like the fix to this problem might be to abandon tables. Convert the tables to ranges, and attach VBA to inserted down-arrow objects. If there were only one column, easy enough, but when there are 71 columns per table, like mine has, sorting becomes massively complex, I would think.
 
Hi ,

Did you mean sorting or filtering ?

When the table is locked and the worksheet is protected , sorting is disallowed , since it will mean that cells and their data will have to be rearranged , but filtering is still possible , since all that it involves is hiding and unhiding rows of data.

Narayan
 
NARAYANK991 said:
Hi ,

Did you mean sorting or filtering ?

When the table is locked and the worksheet is protected , sorting is disallowed , since it will mean that cells and their data will have to be rearranged , but filtering is still possible , since all that it involves is hiding and unhiding rows of data.

Narayan
Click to expand...
Sorting is what I mean, not filtering.
 
That's the ticket! I should have seen that all along, it seems obvious now.
First, unlock and unhide all cells. Next, Protect the sheet with only these two options:
  • Allow Sort
  • Allow Autofilter
That's it; you don't even need users to be allowed to edit ranges. They can't select any cells, so they can't overwrite anything. And they can't insert columns or rows, either. Done! Thanks, Narayan!
 
Kenneth Hobson said:
Looks like nice work. What columns or exact cells did you want to sort and sort ascending or descending?

This code shows C5 sorting both ways. I did not know which you wanted or how you wanted to run it. Mainly, one records a macro and then sets the sheet's Protect options as explained earlier to get started.

The code can be cleaned up a bit but with ListObjects(), table, code is a bit more involved. Luckily, the macro recorder makes doing it fairly easy.
Code: 
Sub Macro2()
'c5 sorts...
    ActiveSheet.Unprotect
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Click to expand...
Kenneth Hobson said:
Looks like nice work. What columns or exact cells did you want to sort and sort ascending or descending?

This code shows C5 sorting both ways. I did not know which you wanted or how you wanted to run it. Mainly, one records a macro and then sets the sheet's Protect options as explained earlier to get started.

The code can be cleaned up a bit but with ListObjects(), table, code is a bit more involved. Luckily, the macro recorder makes doing it fairly easy.
Code: 
Sub Macro2()
'c5 sorts...
    ActiveSheet.Unprotect
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Clear
    ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal").Sort. _
        SortFields.Add Key:=Range("InptTotal[[#All],[ TOTAL INPATIENT BY MDC]]"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Svc Line Analysis").ListObjects("InptTotal"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Click to expand...
Kenneth, thanks so much for working on this, but the solution seems to be much simpler, as I posted below.
 
You must log in or register to reply here.
Back
Top