当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - Sorting column triggers click to run Macro...How do I fix this?

问题描述:

I have some code that runs a macro when certain cells are clicked. The problem is that sorting a column also causes the macro to run. How can I prevent this code from running when a column is sorted?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Selection.Count = 1 Then

If Not Intersect(Target, Range("AI13:AI10000")) Is Nothing Then

MsgBox "Hello World!"

End If

End If

End Sub

I'm pretty sure it's irrelevant, but the rest of my code is simply for taking values from one workbook and using it to filter pivot tables in another workbook.

Thanks!

网友答案:

Sorting will trigger Worksheet_SelectionChange because the range is selected in the sorting process. Using Worksheet_BeforeDoubleClick instead will run the macro with a double click on the desired cell and the event will not be triggered by sorting.

The following code worked for me:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("AI13:AI10000")) Is Nothing Then
            MsgBox "Hello World!"
        End If
    End If
End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: