Excel VBA 中的 OnClick

OnClick in Excel VBA

提问by haslo

Is there a way to catch a click on a cell in VBA with Excel? I am not referring to the Worksheet_SelectionChangeevent, as that will not trigger multiple times if the cell is clicked multiple times. BeforeDoubleClickdoes not solve my problem either, as I do not want to require the user to double click that frequently.

有没有办法用Excel捕捉VBA中单元格的点击？我不是指Worksheet_SelectionChange事件，因为如果多次单击单元格，它不会触发多次。BeforeDoubleClick也不能解决我的问题，因为我不想要求用户经常双击。

My current solution does work with the SelectionChangeevent, but it appears to require the use of global variables and other suboptimal coding practices. It also seems prone to error.

我当前的解决方案确实适用于该SelectionChange事件，但它似乎需要使用全局变量和其他次优编码实践。它似乎也容易出错。

采纳答案by dbb

Clearly, there is no perfect answer. However, if you want to allow the user to

显然，没有完美的答案。但是，如果您想允许用户

1. select certain cells
2. allow them to change those cells, and
3. trap each click,even repeated clicks on the same cell,

1. 选择某些单元格
2. 允许他们更改这些单元格，并且
3. 捕获每次点击，甚至重复点击同一个单元格，

then the easiest way seems to be to move the focus off the selected cell, so that clicking it will trigger a Select event.

那么最简单的方法似乎是将焦点从所选单元格上移开，以便单击它会触发 Select 事件。

One option is to move the focus as I suggested above, but this prevents cell editing. Another option is to extend the selection by one cell (left/right/up/down),because this permits editing of the original cell, but will trigger a Select event if that cell is clicked again on its own.

一种选择是按照我上面的建议移动焦点，但这会阻止单元格编辑。另一种选择是将选择范围扩大一个单元格（左/右/上/下），因为这允许编辑原始单元格，但如果再次单击该单元格，则会触发 Select 事件。

If you only wanted to trap selection of a single column of cells, you could insert a hidden column to the right, extend the selection to include the hidden cell to the right when the user clicked,and this gives you an editable cell which can be trapped every time it is clicked. The code is as follows

如果您只想捕获单列单元格的选择，您可以在右侧插入一个隐藏列，当用户单击时扩展选择以包括隐藏的单元格，这为您提供了一个可编辑的单元格，可以每次点击都会被困住。代码如下

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'prevent Select event triggering again when we extend the selection below
  Application.EnableEvents = False
  Target.Resize(1, 2).Select
  Application.EnableEvents = True
End Sub

回答by dbb

In order to trap repeated clicks on the same cell, you need to move the focus to a different cell, so that each time you click, you are in fact moving the selection.

为了捕获对同一单元格的重复点击，您需要将焦点移动到不同的单元格，这样每次点击时，您实际上是在移动选择。

The code below will select the top left cell visible on the screen, when you click on any cell. Obviously, it has the flaw that it won't trap a click on the top left cell, but that can be managed (eg by selecting the top right cell if the activecell is the top left).

当您单击任何单元格时，下面的代码将选择屏幕上可见的左上角单元格。显然，它有一个缺陷，它不会捕获左上角单元格上的点击，但可以管理（例如，如果活动单元格位于左上角，则通过选择右上角的单元格）。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  'put your code here to process the selection, then..
  ActiveWindow.VisibleRange.Cells(1, 1).Select
End Sub

回答by Mike Rosenblum

SelectionChange is the event built into the Excel Object model for this. It should do exactly as you want, firing any time the user clicks anywhere...

SelectionChange 是为此在 Excel 对象模型中内置的事件。它应该完全按照您的意愿进行，在用户点击任何地方时触发......

I'm not sure that I understand your objections to global variables here, you would only need 1 if you use the Application.SelectionChange event. However, you wouldn't need any if you utilize the Workbook class code behind (to trap the Workbook.SelectionChange event) or the Worksheet class code behind (to trap the Worksheet.SelectionChange) event. (Unless your issue is the "global variable reset" problem in VBA, for which there is only one solution: error handling everywhere. Do not allow any unhandled errors, instead log them and/or "soft-report" an error as a message box to the user.)

我不确定我是否理解您对全局变量的反对，如果您使用 Application.SelectionChange 事件，您只需要 1。但是，如果您利用后面的 Workbook 类代码（捕获 Workbook.SelectionChange 事件）或后面的 Worksheet 类代码（捕获 Worksheet.SelectionChange）事件，则不需要任何代码。（除非您的问题是 VBA 中的“全局变量重置”问题，对此只有一种解决方案：处处错误处理。不允许任何未处理的错误，而是将它们记录下来和/或“软报告”错误作为消息框给用户。）

You might also need to trap the Worksheet.Activate() and Worksheet.Deactivate() events (or the equivalent in the Workbook class) and/or the Workbook.Activate and Workbook.Deactivate() events so that you know when the user has switched worksheets and/or workbooks. The Window activate and deactivate events should make this approach complete. They could all call the same exact procedure, however, they all denote the same thing: the user changed the "focus", if you will.

您可能还需要捕获 Worksheet.Activate() 和 Worksheet.Deactivate() 事件（或 Workbook 类中的等效事件）和/或 Workbook.Activate 和 Workbook.Deactivate() 事件，以便您知道用户何时有切换工作表和/或工作簿。Window 激活和停用事件应该使这种方法完整。它们都可以调用完全相同的过程，但是，它们都表示相同的事情：用户更改了“焦点”，如果您愿意的话。

If you don't like VBA, btw, you can do the same using VB.NET or C#.

如果你不喜欢 VBA，顺便说一句，你可以使用 VB.NET 或 C# 做同样的事情。

[Edit: Dbb makes a very good point about the SelectionChange event not picking up a click when the user clicks within the currently selected cell. If you need to pick that up, then you would need to use subclassing.]

[编辑：Dbb 很好地说明了当用户在当前选定的单元格内单击时 SelectionChange 事件没有选择单击。如果您需要选择它，那么您将需要使用子类化。]

回答by TcKs

I don't think so. But you can create a shape object ( or wordart or something similiar ) hook Click event and place the object to position of the specified cell.

我不这么认为。但是您可以创建一个形状对象（或艺术字或类似的东西）挂钩 Click 事件并将对象放置到指定单元格的位置。

回答by Joe

This has worked for me.....

这对我有用......

Private Sub Worksheet_Change(ByVal Target As Range)

    If Mid(Target.Address, 3, 1) = "$" And Mid(Target.Address, 2, 1) < "E" Then
       ' The logic in the if condition will filter for a specific cell or block of cells
       Application.ScreenUpdating = False
       'MsgBox "You just changed " & Target.Address

       'all conditions are true .... DO THE FUNCTION NEEDED 
       Application.ScreenUpdating = True
    End If
    ' if clicked cell is not in the range then do nothing (if condttion is not run)  
End Sub

NOTE: this function in actual use recalculated a pivot table if a user added a item in a data range of A4 to D500. The there were protected and unprotected sections in the sheet so the actual check for the click is if the column is less that "E" The logic can get as complex as you want to include or exclude any number of areas

注意：如果用户在A4到D500的数据范围内添加了一个项目，该功能在实际使用中会重新计算数据透视表。工作表中有受保护和不受保护的部分，因此实际检查点击的是列是否小于“E”逻辑可以变得复杂到您想要包含或排除任意数量的区域

block1  = row > 3 and row < 5 and column column >"b" and < "d" 
block2  = row > 7 and row < 12 and column column >"b" and < "d" 
block3  = row > 10 and row < 15 and column column >"e" and < "g"

If block1 or block2 or block 3 then
  do function .....
end if  

回答by Dumitru Daniel

I had a similar issue, and I fixed by running the macro "onTime", and by using some global variables to only run once the user has stopped clicking.

我有一个类似的问题，我通过运行宏“onTime”来解决，并使用一些全局变量只在用户停止点击后运行。

Public macroIsOnQueue As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    macroIsOnQueue = False
    Application.OnTime (Now() + TimeValue("00:00:02")), "addBordersOnRow"
    macroIsOnQueue = True
End sub
Sub addBordersOnRow()
    If macroIsOnQueue Then        
       macroIsOnQueue = False
       ' add code here
    End if
End sub

This way, whenever the user changes selection within 2 seconds, the macroIsOnQueue variable is set to false, but the last time selection is changed, macroIsOnQueue is set to true, and the macro will run.

这样，每当用户在 2 秒内更改选择时，macroIsOnQueue 变量设置为 false，但上次更改选择时，macroIsOnQueue 设置为 true，宏将运行。

Hope this helps, Have fun with VBA !!

希望这会有所帮助，VBA 玩得开心！！

回答by Super Symmetry

Just a follow-up to dbb's accepted answer: Rather than adding the immediate cell on the right to the selection, why not select a cell way off the working range (i.e. a dummy cell that you know the user will never need). In the following code cell ZZ1is the dummy cell

只是 dbb 接受的答案的后续行动：与其将右侧的直接单元格添加到选择中，为什么不选择远离工作范围的单元格（即您知道用户永远不需要的虚拟单元格）。在下面的代码单元格ZZ1是虚拟单元格

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.EnableEvents = False
  Union(Target, Me.Range("ZZ1")).Select
  Application.EnableEvents = True

  ' Respond to click/selection-change here

End Sub