表 - Worksheet_Change多次触发 [英] Tables - Worksheet_Change Fires Multiple Times

问题描述

当用户清除表标题时，我正在尝试捕获工作表更改事件。

I'm trying to capture the worksheet change event when a table heading is cleared by the user.

Private Sub Worksheet_Change(ByVal Target As Range)
application.EnableEvents = False    
    If Not Intersect(Target, Me.ListObjects("Table1").HeaderRowRange) Is Nothing Then
        msgbox "Hello"
    end if
application.EnableEvents = True  
End Sub 

如果用户按删除清除表上的非默认标题名称，Excel会自动将空白标题替换为默认标题名称（例如Column1）。这似乎导致工作表更改事件多次运行。我想知道一个方法，当用户清除标题时，这个事件只运行一次。

If the user presses "delete" to clear a non-default heading name on the table, Excel automatically replaces the blank heading with the default heading name (eg. "Column1"). This seems to cause the worksheet change event to run multiple times. I'd like to figure out a way to have this event run only once when the user clears the a heading.

非常感谢任何帮助。

推荐答案

最简单的解决方法是在事件开始时添加断言，检查目标单元格是否不已经包含一个默认列名称。

The easiest workaround for this is to add an assertion at the beginning of your event, that checks that the target cell does not already contain a default column name.

如果和我将使用简单的如语句，当标题以Column开头，并使用 Exit Sub 退出事件时捕获。您可以在一个简单的代码行中完成所有这些。像...一样...

I would do this with a simple if and like statement, which catches when a heading begins with "Column" and exits the event using Exit Sub. You could do all of this in one simple line of code. Something like...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.value Like "Column*" Then Exit Sub
application.EnableEvents = False    
    If Not Intersect(Target, Me.ListObjects("Table1").HeaderRowRange) Is Nothing Then
        msgbox "Hello"
    end if
application.EnableEvents = True  
End Sub 

这段代码是未经测试的但它应该为您提供从...开始的良好基础。

This code is admittedly untested but it should provide you with a good base to start from.

这篇关于表 - Worksheet_Change多次触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！