How to prevent a table from sorting (and that's it)

[Scotty81]

I have a simple Excel table that I need to prevent from resorting. I still need to be able to filter and enter data on it. I thought I could unlock cells and then protect the worksheet and just uncheck sorting from the protect worksheet menu, but that seems to cause Excel to be unresponsive. I thought that would be straight forward, but not really finding what I want from a Google search?

Is this easily doable without VBA? If VBA is required, that's OK too.

 

[Yongle]

How this method works
Add a column to the table and FORCE the table to be sorted by that column
Sorting does not trigger an event
So a volatile formula is added to the sheet to trigger Worksheet_Calculate event whenever sorting occurs

To test
Do the following in this sequence
- create a NEW workbook
- paste the data in A1:D11 below into sheet1
- convert that range into a Table
- place VBA below into the SHEET module ( right-click sheet tab \ View Code \ paste code into the new window \ {ALT}{F11} to go back to Excel )
- and finally the "magic" ...
- in cell F1 enter this formula and see what happens to the data in the table
=TODAY()

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    SortTable
    Application.EnableEvents = True
End Sub

Private Sub SortTable()
    Dim tbl As ListObject
    Set tbl = Me.ListObjects(1)
    With tbl.Sort
       .SortFields.Clear
       .SortFields.Add Key:=tbl.Range(1, 1), SortOn:=xlSortOnValues, Order:=xlAscending
       .Header = xlYes
       .Apply
    End With
End Sub

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Sort[/td][td=bgcolor:#4472C4]Date[/td][td=bgcolor:#4472C4]Leader[/td][td=bgcolor:#4472C4]Amount[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]

10​

[/td][td=bgcolor:#D9E1F2]

29/08/2019​

[/td][td=bgcolor:#D9E1F2]Dog[/td][td=bgcolor:#D9E1F2]

111​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

5​

[/td][td]

31/08/2019​

[/td][td]Puma[/td][td]

210​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]

4​

[/td][td=bgcolor:#D9E1F2]

29/08/2019​

[/td][td=bgcolor:#D9E1F2]Lion[/td][td=bgcolor:#D9E1F2]

318​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]

7​

[/td][td]

31/08/2019​

[/td][td]Cat[/td][td]

320​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td=bgcolor:#D9E1F2]

9​

[/td][td=bgcolor:#D9E1F2]

30/08/2019​

[/td][td=bgcolor:#D9E1F2]Lion[/td][td=bgcolor:#D9E1F2]

394​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]

2​

[/td][td]

02/09/2019​

[/td][td]Cat[/td][td]

614​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td=bgcolor:#D9E1F2]

3​

[/td][td=bgcolor:#D9E1F2]

29/08/2019​

[/td][td=bgcolor:#D9E1F2]Puma[/td][td=bgcolor:#D9E1F2]

713​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

6​

[/td][td]

31/08/2019​

[/td][td]Lion[/td][td]

713​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td=bgcolor:#D9E1F2]

1​

[/td][td=bgcolor:#D9E1F2]

29/08/2019​

[/td][td=bgcolor:#D9E1F2]Dog[/td][td=bgcolor:#D9E1F2]

771​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]

8​

[/td][td]

30/08/2019​

[/td][td]Cat[/td][td]

981​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sort[/td][/tr][/table]