使用Set Range排序：首次运行后KeyRange永久锁定的问题

Hey there, let's break down why your sorting code is getting stuck on the first clicked column, and how to fix it.

The Root Cause #

From what you described, the problem almost certainly comes from two key issues:

1. Uncleared sort fields: Excel retains previous sorting conditions by default—if you don't wipe these clean before each new sort, old KeyRange settings will linger and interfere with new operations.
2. Static KeyRange assignment: If your original code uses a global variable to store the clicked column and doesn't reassign it on each double-click, it'll keep using the first value it got forever.

The Solution: Reset Sort Conditions & Use Dynamic Target References #

Here's a revised version of your code that fixes both problems. It clears old sort settings every time you double-click a header, then dynamically uses the clicked column as the second sort key:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' Only run if we're double-clicking the header row (adjust row number if your header isn't row 1)
    If Target.Row <> 1 Then Exit Sub
    
    Cancel = True ' Prevent the default double-click cell edit behavior
    
    ' Step 1: Clear ALL existing sort fields to eliminate old settings
    ActiveSheet.Sort.SortFields.Clear
    
    ' Step 2: Add the primary sort (D column)
    ActiveSheet.Sort.SortFields.Add _
        Key:=Me.Range("D1"), _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, ' Change to xlDescending if you want reverse order
        DataOption:=xlSortNormal
    
    ' Step 3: Add the secondary sort (the column you just double-clicked)
    ActiveSheet.Sort.SortFields.Add _
        Key:=Target, _
        SortOn:=xlSortOnValues, _
        Order:=xlAscending, ' Adjust order as needed
        DataOption:=xlSortNormal
    
    ' Apply the sort to your data range (uses CurrentRegion to auto-detect your table)
    With Me.Sort
        .SetRange Me.Range("A1").CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Key Fixes Explained #

• SortFields.Clear: This is the most critical line. It wipes out any leftover sort conditions from previous runs, so each double-click starts with a clean slate.
• Using Target directly: Instead of storing the clicked column in a persistent variable, we use the Target parameter (which automatically references the clicked cell) for the secondary sort key. This ensures we always use the most recent clicked column.
• Me instead of ActiveSheet: Using Me refers directly to the worksheet the code is attached to, which is more reliable than relying on the active sheet (in case the user clicks another sheet mid-operation).

Quick Troubleshooting Tip #

If your original code used a global KeyRange variable, make sure you're reassigning it every time the double-click event fires (e.g., Set KeyRange = Target at the start of the sub). But honestly, ditching the global variable entirely and using Target like the example above is a cleaner, more maintainable fix.

内容的提问来源于stack exchange，提问作者Joshua Taylor