使用设置范围进行排序，但在使用一次之后不会更改该值(Using a Set Range to Sort, but it never changes the value after using it once)

 我在这里有一个代码用于其基本目标...双击标题将按列D进行排序，然后按列双击进行排序。 
 
 是的，我知道这可以通过多重排序更容易地完成，但这不是...的原因选项。 
 
 问题是，在代码运行一次之后，它的工作状态很好，它“似乎”永久性地将我的“Set KeyRange”设置为点击值。 
 
 如果我手动使用表格并尝试双击一个新标题，它仍会根据首次选择的标题对其进行分类。 
 
 我怎样才能解决这个问题？ 谢谢。 我试着在结尾附近添加一个新的Set Key范围命令，但是这个命令什么都没做，或者破坏了它。 
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("A1:D12").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
End If
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("D1"), Order:=xlAscending
     .SortFields.Add Key:=KeyRange, Order:=xlAscending
     .SetRange Range("A1:D12")
     .Header = xlYes
     .Apply
End With
End Sub

I have a code here that works for its base goal... Double clicking on a header will sort by Column D, then Sort by the Column Double Clicked.
 
Yes, I know that this can be more easily accomplished through a Multiple Sort, but thats not an option for...reasons.
 
The problem, is that after the code has been run once, where it works just fine, it "seems" to be permenantly setting my "Set KeyRange" to the clicked value.
 
If I manually resort the table, and try to Doubleclick a new header, it still sorts it according to the header selected the first time.
 
How can I fix this? Thanks. I tried adding a new Set Key range command near the end, but that did nothing, or broke it.
 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim KeyRange As Range
Dim ColumnCount As Integer
ColumnCount = Range("A1:D12").Columns.Count
Cancel = False
If Target.Row = 1 And Target.Column <= ColumnCount Then
Cancel = True
Set KeyRange = Range(Target.Address)
End If
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("D1"), Order:=xlAscending
     .SortFields.Add Key:=KeyRange, Order:=xlAscending
     .SetRange Range("A1:D12")
     .Header = xlYes
     .Apply
End With
End Sub

原文：https://stackoverflow.com/questions/48757067