Results 1 to 2 of 2

Thread: How to prevent a table from sorting (and that's it)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2006
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,917
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

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

    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()

    Code:
    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
    A
    B
    C
    D
    E
    1
    Sort Date Leader Amount
    2
    10
    29/08/2019
    Dog
    111
    3
    5
    31/08/2019
    Puma
    210
    4
    4
    29/08/2019
    Lion
    318
    5
    7
    31/08/2019
    Cat
    320
    6
    9
    30/08/2019
    Lion
    394
    7
    2
    02/09/2019
    Cat
    614
    8
    3
    29/08/2019
    Puma
    713
    9
    6
    31/08/2019
    Lion
    713
    10
    1
    29/08/2019
    Dog
    771
    11
    8
    30/08/2019
    Cat
    981
    12
    Sheet: Sort

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •