使用Excel VBA事件检测单元格的合并/取消合并? [英] Using Excel VBA Events to detect merging/unmerging of cells?

问题描述

我正在尝试找到一种方法来检测合并(或未合并)单元格的立即使用.更改事件不会触发selectionchange.我尝试了其他一些方法，但是合并似乎没有事件触发-我觉得很奇怪.我在change事件中的代码当前基于单元格的内容更改Interior.Color.如果将单元格合并然后取消合并，则颜色将保留在整个选择范围内.但是我希望它仅将颜色保留在带有文本的单元格中，然后在其余部分返回xlNone.无论如何要做到这一点?

I am trying to find a way to detect the immediate use of merging (or unmerging) cells. The change event triggers nor does the selectionchange. I've tried some of the other, but it seems no events trigger upon the merge - which I find odd. My code in the change event currently changes Interior.Color based off the contents of the cell. If the cell is merged and then unmerged, the color stays across the selection. But I would like it to keep the color only in the cell with the text and go back to xlNone on the rest. Anyway to pull this off?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Then Exit Sub
If Target.Row <> 3 Then Exit Sub
Select Case Target.Text
    Case "Blackberry Serrano", "BS"
        Target.Interior.Color = RGB(120, 33, 111)
    [...more of the same...]
    Case ""
        Target.Interior.Color = xlNone
End Select
End Sub

推荐答案

据我所知，尚无法检测单元格上格式的更改.合并被认为是格式更改，因此无法通过VBA事件检测到.但是，我做了一些挖掘，发现了这个有趣的列表:

As far as I know, there is no way to detect formatting changes on a cell. Merging is considered a format change and is therefore undetectable via VBA events. However, I did some digging, and found this interesting list:

• 更改单元格的格式不会触发Change事件(如预期的那样).但是复制和粘贴格式确实会触发 变更事件.选择主页=>编辑=>清除=>清除格式 命令也会触发事件.
• 即使在此过程中删除了某些合并单元格的内容，合并单元格也不会触发Change事件.
• 添加，编辑或删除单元格注释不会触发Change事件.
• 即使单元格为空，按Delete键也会生成一个事件.
• 使用Excel命令更改的单元格可能会或可能不会触发Change事件.例如，对范围进行排序或使用目标 寻求更改单元格的搜索者不会触发该事件.但是使用 拼写检查器可以.
• 如果您的VBA过程更改了单元格的内容，则会确实触发Change事件.

• Changing the formatting of a cell doesn't trigger the Change event (as expected). But copying and pasting formatting does trigger the Change event. Choosing the Home=>Editing=>Clear=>Clear Formats command also triggers the event.
• Merging cells doesn't trigger the Change event, even if the contents of some of the merged cells are deleted in the process.
• Adding, editing or deleting a cell comment doesn't trigger the Change event.
• Pressing Delete generates an event even if the cell is empty to start with.
• Cells that are changed by using Excel commands may or may not trigger the Change event. For example, sorting a range or using Goal Seeker to change a cell does not trigger the event. But using the spell checker does.
• If your VBA procedure changes the contents of a cell, it does trigger the Change event.

摘自John Walkenbach的使用VBA进行Excel 2013 Power编程

From Excel 2013 Power Programming with VBA by John Walkenbach Source

因此，基本上，如果有人正在合并或取消合并单元格，则无法通过VBA事件检测到这一点.

So basically, if someone is just merging or unmerging cells, there is no way to detect that through a VBA event.

这篇关于使用Excel VBA事件检测单元格的合并/取消合并?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！