UsefulSheetCodes

'Useful algorthms for locked sheet, to allow sorting, filtering, grouping etc

'*** UNPROTECT SHEET AND CHECK FOR ALLOWED EDIT RANGES ***
Sheets("Main").Unprotect "password"

'check if the sheet has edit ranges (to allow sorting through cell lock)
If Sheets("Main").Protection.AllowEditRanges.Count <> 0 Then
    For i = 1 To Sheets("Main").Protection.AllowEditRanges.Count
        ActiveSheet.Protection.AllowEditRanges(i).Delete
    Next i
End If
'*********************************************************

'*** LOCK SHEET, ALLOW SORTING AND GROUPING, BUT DISALLOW LOCKED CELLS SELECTION ***
'add allowed edit range to allow users to sort sheet
Sheets("Main").Protection.AllowEditRanges.Add Title:="AllowSort", Range:=Range(Cells(lDataRow_Main + 1, 1), Cells(lLRow_Main, lLCol_Main))
'reference : https://stackoverflow.com/questions/10197772/how-to-lock-specific-cells-but-allow-filtering-and-sorting/15390698#15390698
'NOTE:  Solution above may be sufficient, but users can still technically mess up locked ranges if they select an unlocked range and locked range together
        (to simulate - Select unlocked cell, drag into locked cell range and delete selection. Deletion will occur). To circumvent this issue, can look reference 2.
'reference 2 : https://stackoverflow.com/questions/10197772/how-to-lock-specific-cells-but-allow-filtering-and-sorting/50496626#50496626

'password protect the sheet
Sheets("Main").Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowSorting:=True, AllowFiltering:=True, UserInterfaceOnly:=True, _
    AllowFormattingColumns:=True, AllowFormattingRows:=True
    
'enable grouping
Sheets("Main").EnableOutlining = True

'disable selecting locked cells
Sheets("Main").EnableSelection = xlUnlockedCells
************************************************************************************


'To remove grouping, 
Range(Cells(lHeadRow_Main, 1), Cells(Range(lastCellAdd).Row, Range(lastCellAdd).Column)).ClearOutline

'To reset cell widths and height
Cells.UseStandardHeight = True
Cells.UseStandardWidth = True