隐藏行或列时在Excel VBA中触发事件 [英] Trigger Event in Excel VBA when Row or Column is hidden

问题描述

当我手动隐藏行/列时，是否有一种方法可以触发Excel VBA中的事件(称为子项)?

Is there a way, to trigger an event (call a sub) in Excel VBA, when i manually hide a row/column?

当同一行被隐藏在特定工作表中时，我希望该行被隐藏在所有后续工作表中.

I want the same row to be hidden in all following sheets, when it is hidden in a particular sheet.

有可能吗?

预先感谢

推荐答案

没有直接事件触发器来捕获隐藏或取消隐藏的列.有一些笨拙的解决方法，在单元格中使用公式，但是使用这些公式时感觉就像是在跳动，而且不够灵活.

There is no direct event trigger to capture hiding or unhiding columns. There are clumsy workarounds, using formulae in cells but those feel like a kludge when using and not really flexible.

但是，如果您使用Excel 2007或更高版本，则有一种间接方法来捕获此事件.这很简洁而且非常灵活.

However, there is an indirect way to capture this event if you use Excel 2007 or newer. This is neat and extremely flexible.

1. 修改功能区XML(如果存在):您需要能够修改功能区的customUI14.xml(对于Excel 2010)或customUI.xml(对于Excel 2007).
2. 创建自定义功能区UI XML文件(如果不存在):您可以从
3. 执行动作的VBA代码:添加以下列出的代码，以便在事件捕获后立即采取行动.

1. Modify the Ribbon XML (if it's present): You need to be able to modify the Ribbon's customUI14.xml (for Excel 2010) or customUI.xml (for Excel 2007).
2. Create the custom Ribbon UI XML file (if not present): You may use Ron De Bruin's excellent Custom UI Editor from http://www.rondebruin.nl/win/s2/win001.htm (which is also endorsed in quite a few official Microsoft examples).
3. XML changes for capturing events: Open the Excel file in the Custom UI Editor and add the XML code as shown below. That will enable a column hide or unhide event to trigger a macro in your code.
4. VBA code to execute your actions: Add the code listed below to take action once the event is captured.

参考::出色的解决方案由Andy Pope提供.

Reference: This excellent solution was provided by Andy Pope here (MSDN link).

自定义XML代码:

<customUI  xmlns="http://schemas.microsoft.com/office/2006/01/customui" >
    <commands >
        <command 
            idMso="ColumnsHide"
            onAction="Column_Hide_Macro"/>
        <command 
            idMso="ColumnsUnhide"
            onAction="Column_UnHide_Macro"/>
    </commands >
</customUI>

自定义UI编辑器屏幕截图:

VBA代码:

Sub Column_Hide_Macro(control As IRibbonControl, ByRef CancelDefault)
    MsgBox ("You have hidden a column")

    ' You may put your code here
    ' to check if your monitored row is hidden

    CancelDefault = False   ' This enables the default action to continue
End Sub

Sub Column_UnHide_Macro(control As IRibbonControl, ByRef CancelDefault)
    MsgBox ("You have unhidden a column")

    ' You may put your code here
    ' to check if your monitored row is unhidden

    CancelDefault = False   ' This enables the default action to continue
End Sub

这篇关于隐藏行或列时在Excel VBA中触发事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！