Sorting Issue

[Ev1lZer0]

I have a spreadsheet that I created for someone else to use. There are a few columns that I don't want them to edit as they have formulas in them.

I have found a way to protect these specific columns, but once I do, I am unable to perform a custom sort or even reapply a custom sort.

I have protected the cell using the following method.

1. formatted the cells to be unlocked
2. formatted the columns I need to lock
3. use the "Protech Sheet" option under the review tab.
Only columns A and I are locked with the addition of a few specific cells.

Once locked I can use the filter to apply a filter or sort, but I can't apply a custom sort. For instance sort first by column C then by column Z

Please ask any clarifying questions you may need to know.

One fix I did think of was to create a macro that would unprotect the sheet, then perform the custom sort, then protect it again, but I have NO IDEA how this is done. and this has other implications, like anyone who wants to use it must have the ability to open an excel sheet with a macro.

 

[sijpie]

YEs, that is correct. Filtering and sorting on protected sheets is a pain.

And like you say, the alternative using a macro can be a pain, as the users will have to allow the macro to run (if the company IT allows them to do it). So this certainly can be a problem as well.

But if you want to go the macro route, let me know. Is the custom sort always the same? Or are the users making their own sort? Or are there a limited number of custom sorts?

 

[Ev1lZer0]

I think I would like to go the macro route. We may not use it ultimately, but at least I can try.

To answer your questions, The custom sort would typically always be the same - 1st by triage column C oldest to newest then by column Z smallest to largest.

If there was a way simple way to write the macro to allow the user to choose the custom sort, that would be pretty cool, but ultimately, not necessary.

 

[sijpie]

I am working on it.

 

[Ev1lZer0]

I appreciate your work. I hope this is useful as I wouldn't suspect you need any data.

2022 Color Coded Watch List Copy.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Date of Trial	Triage Date	TSO Date	Case Number	First Name	Last Name	NFC	In Custody	In Custody Date	Plea & Sentence	Competency Hearing	Pre-Trial	Readiness/Status	Continuances	Omnibus	Other Hearing Dates	Offer	Time Frame	Attorneys	NOTES	Order on Competency Evaluation	Outcome	Outcome Date	Color Code	Trailing
2022 Trial Calendar

 

[sijpie]

Thanks for the headers.

 

[sijpie]

To carry out a sort on a protected sheet I have created a userform where the user can enter the column(s) to sort on. Below is an image of the userform, together with the names of each of the controls.

In the VBA below, check the comments starting with <<<<<<< as your input may be required here

The VBA for the userform :

Option Explicit

Const sASC As String = "Ascending", sDESC As String = "Descending"

Private Sub CommandButton1_Click()
    'reset form button
    UserForm_Activate
End Sub
Private Sub CommandButton2_Click()
' Remove 2nd sort column
    EnableComboBox ComboBox2, False
End Sub
Private Sub CommandButton3_Click()
' Remove 3rd sort column
    EnableComboBox ComboBox3, False
End Sub

Private Sub CommandButton4_Click()
' plus button (add 2nd sort column)
    EnableComboBox ComboBox2, True
    
End Sub
Private Sub CommandButton5_Click()
' plus button (add 3rd sort column)
    EnableComboBox ComboBox3, True
    
End Sub

Private Sub CommandButton6_Click()
' Sort button
    
    Dim rTbl As Range
    
    
    Set rTbl = ActiveSheet.Range("C2").CurrentRegion
    With rTbl
        Select Case True
            Case ComboBox3.Enabled
                .Sort key1:=.Cells(1, ComboBox1.ListIndex + Me.Tag).EntireColumn, order1:=IIf(SpinButton1.Value = 1, xlAscending, xlDescending), _
                    key2:=.Cells(1, ComboBox2.ListIndex + Me.Tag).EntireColumn, order2:=IIf(SpinButton2.Value = 1, xlAscending, xlDescending), _
                    key3:=.Cells(1, ComboBox3.ListIndex + Me.Tag).EntireColumn, order3:=IIf(SpinButton3.Value = 1, xlAscending, xlDescending), Header:=xlYes
            Case ComboBox2.Enabled
                .Sort key1:=.Cells(1, ComboBox1.ListIndex + Me.Tag).EntireColumn, order1:=IIf(SpinButton1.Value = 1, xlAscending, xlDescending), _
                    key2:=.Cells(1, ComboBox2.ListIndex + Me.Tag).EntireColumn, order2:=IIf(SpinButton2.Value = 1, xlAscending, xlDescending), Header:=xlYes
            Case Else
                .Sort key1:=.Cells(1, ComboBox1.ListIndex + Me.Tag).EntireColumn, order1:=IIf(SpinButton1.Value = 1, xlAscending, xlDescending), Header:=xlYes
        End Select
    End With
    
End Sub

Private Sub CommandButton7_Click()
'Cancel buttonn
    Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    ' this runs on closing the form using close button or red X button
    ProtectSht ActiveSheet.Name
    
End Sub


Private Sub SpinButton1_Change()
    ' spin button for 1st combo sort direction
    If SpinButton1.Value > 1 Then SpinButton1.Value = 1
    Select Case SpinButton1.Value
     Case 0
        Label1.Caption = sDESC
     Case 1
        Label1.Caption = sASC
     End Select
        
End Sub
Private Sub SpinButton2_Change()
    ' spin button for 2nd combo sort direction
    If SpinButton2.Value > 1 Then SpinButton2.Value = 1
    Select Case SpinButton2.Value
     Case 0
        Label2.Caption = sDESC
     Case 1
        Label2.Caption = sASC
     End Select
        
End Sub
Private Sub SpinButton3_Change()
     ' spin button for 3rd combo sort direction
   If SpinButton3.Value > 1 Then SpinButton3.Value = 1
    Select Case SpinButton3.Value
     Case 0
        Label3.Caption = sDESC
     Case 1
        Label3.Caption = sASC
     End Select
        
End Sub



Sub HeadingComboReset(ctrlCombo As MSForms.ComboBox)
' reset a combobox
    Dim vH, vCL
    Dim lR As Long, UB1 As Long, iOff As Integer
    Dim rTL As Range
    Dim sCL As String
    
    With ActiveSheet.Range("C1")        '<<<<<<<<<< cell address of a header cell <<<<<<
        Set rTL = .CurrentRegion.Cells(1, 1)
        
        'load header into array
        vH = WorksheetFunction.Transpose(rTL.Resize(1, .CurrentRegion.Columns.Count))
    End With
    UB1 = UBound(vH, 1)
    
    ' calculate offset in case header does not start in column A
    iOff = rTL.Column - 1
    
    'add Column letter to header text
    For lR = 1 To UB1
        vH(lR, 1) = ColLetter(lR + iOff) & "  " & vH(lR, 1)
    Next lR

    ' fill combobox with headers
    With ctrlCombo
        .Clear
        .List = vH
        .ListIndex = 0
    End With
    
    'store iOff in me.tag to use in other subs
    Me.Tag = iOff
End Sub


Private Sub UserForm_Activate()
    
    UnprotectSht ActiveSheet.Name

    HeadingComboReset ComboBox1
    EnableComboBox ComboBox2, True
    
    ComboBox1.ListIndex = 3 - Me.Tag - 1    '<<<< default column C 
    ComboBox2.ListIndex = 26 - Me.Tag - 1   '<<<< default column Z
    
End Sub


Private Sub UserForm_Initialize()

    EnableComboBox ComboBox2, False
    EnableComboBox ComboBox3, False
End Sub

Sub EnableComboBox(cbCombo As MSForms.ComboBox, bYes As Boolean)
' sets a combobox to enabled / disable including the visibility of associated buttons and text
    Dim iNr As Integer
    Dim ctlBin As MSForms.CommandButton, ctlSpin As MSForms.SpinButton, _
        ctlPlus As MSForms.CommandButton, ctlCtl As MSForms.Control, ctlLbl As MSForms.Label
    
    
    iNr = Right(cbCombo.Name, 1)
    On Error Resume Next        'not every set of controls has each type
    Set ctlBin = Me.Controls("CommandButton" & iNr)
    Set ctlPlus = Me.Controls("CommandButton" & iNr + 3)
    Set ctlSpin = Me.Controls("SpinButton" & iNr)
    Set ctlLbl = Me.Controls("Label" & iNr)
    
    HeadingComboReset cbCombo
    cbCombo.Enabled = bYes
    ctlBin.Visible = bYes
    ctlPlus.Visible = bYes
    ctlSpin.Visible = bYes
    ctlSpin.Value = 1
    ctlLbl.Visible = bYes
    ctlLbl.Caption = sASC
    On Error GoTo 0
    CommandButton6.Visible = True
End Sub

Then the code for a standard module:

Option Explicit

Private Const sPW As String = ""     '<<<<<<<<<<<<<<<  Enter your sheet password here between the "" <<<<<<<<<<<<

'== <<<<<<<<<<<<<<<<  sub to be called by botton on sheet >>>>>>>>>>>>>>==
Sub SortTable()
    UserForm1.Show
End Sub

'== subs to protect / unprotect a worksheet
Sub ProtectSht(sName)
    With Sheets(sName)
        Sheet1.Protect sPW
    End With
End Sub

Sub UnprotectSht(sName)
    With Sheets(sName)
        Sheet1.Unprotect sPW
    End With
End Sub


'== Function to return the column letters of any column in an Excel workbook ==
Function ColLetter(lColNr As Long) As String
    Dim vCL
    Dim i As Integer, m As Integer, k As Integer
    Const sCOLUMNS As String = "Z,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y"
    
    
    vCL = Split(sCOLUMNS, ",")
    
    i = lColNr \ 26     'use \ as division to get integer division (no rounding up)
    m = lColNr Mod 26
    If m = 0 Then i = i - 1 'if m = 0 column ends with letter Z
    Select Case i
        Case 0          ' single letter column
            ColLetter = vCL(m)
        Case Is < 27    ' double letter column
            ColLetter = ColLetter(CLng(i)) & vCL(m)
        Case Else       ' triple letter column
            k = lColNr \ 676
            ColLetter = ColLetter(CLng(k)) & ColLetter(lColNr Mod 676)
    End Select
    
End Function

 

[Ev1lZer0]

Perhaps this is not the right thread to ask this on.

BUT

I have implemented simple VBA scripts before (see code below), but this one seems more complicated.

Function LastSavedTimeStamp() As Date
LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

Here is where I placed your code - in module 3. The code above is modules 2 and 3. Additionally, if this is the right spot, how to I 'activate' the code and have the sort box show up prompting me to sort?

 

[sijpie]

You put this code in a module, which is correct. But you will also need to build the Userform as shown, and add the Userform code to the Userform module.


I sent you a private message with my email suggesting I send you a working workbook, which will be easier for you to implement. Please look in your private messages.